Need help in setting a column up in our module. We have four separate columns , Cons_Last Month, Cons_Last Quarter, Cons_Last_Half Year, Cons_Last_Year, which are the numbers of past historical consumption quantity of a particular sku. For better presentation, we want to show all these under one column header ' Consumption' as shown in the attached screen shot. Any help on how to achieve is appreciated.
The easiest way to do this by having a custom time list and mapping it with your actual line items by creating a line item subset.
Create a custom list for "month", "quarter", "Half-year" and "year" and then create a line item subset for your separate columns, Cons_Last Month, Cons_Last Quarter, Cons_Last_Half Year, Cons_Last_Year.
Map these two in a separate system module.
Now with the simple lookups, you can have the final view as per your screenshot.
You need to map each item from you new list to a month time period in a time system module dimensioned by month.
Once you have this you can direct you function in the target line item to this mapping to determine how the function sums up across the respective time periods.
You will also need to use a nested IF...THEN...ELSE function to determine which time mapping to use based on the item in the list ( Cons_Last Month, Cons_Last Quarter, Cons_Last_Half Year, Cons_Last_Yearm ) and additional mapping table.
The best way to do this is to create a mapping module with this list as a dimension and four line items corresponding to each list item.
Format the line items as boolean and check them as TRUE for each match against the list.
In you target module you can now use four nested IF...THEN...ELSE to cycle through each list item using the boolean line items in this new mapping to determine which time mapping line item should be used in the SUM.
The formula will look something like;
=IF LIST MAPPING MODULE. LAST MONTH BOOLEAN THEN SOURCE[SUM:TIME MAPPING.Cons_Last Month] ELSE IF LIST MAPPING MODULE.Last Quarter BOOLEAN THEN SOURCE[SUM:TIME MAPPING.Cons_Last Quarter] ELSE....
Continue until you have cycled through all the relevant list items.
This solution assumes your target module contains a time month dimension which is required for the time mappings to operate correctly.
Before implementing this I would question why the user needs the data to be presented this way!!! Often users will insist on an output to be exactly the way they are used to seeing it without questioning why. Can the user still extract the same meaning from the data as it is currently presented?
Here I have attached the mock screens for your reference.
Please note: your set up might slightly vary based on your dimensions and other non-accountable components. However, this will help.
You can also try as @ChrisHeathcote suggested. However, it might get little complicated since you need to turn all the summaries for a time in the time settings. Which might not be needed considering the model size.
Collect() formula collects all the data from the source module to the LIS list module ( Meaning you should include all the dimensions along with the LIS list and one line item to collect the data)
For example, dimensions in your source are List A, List B, Time, Line items( Last_Month, Last_Quarter, Half-year, and Year) then your collect module should include List A, List B, Time, LIS, and one line item to collect the data.