Need help in setting the column

Highlighted
Contributor

Need help in setting the column

Hi 

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.

 

Thanks

Regards

Prabakaran

5 REPLIES 5
Highlighted
Frequent Contributor

Re: Need help in setting the column

@praba71,

 

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.

 
 

LIS mapping.PNG

lis mapping 2.PNG

 

Now with the simple lookups, you can have the final view as per your screenshot.

 

Let me know if you need any help.

 

Thanks,

Sandeep

Highlighted
Contributor

Re: Need help in setting the column

Hi @sandeep_bk  Thanks for the response.

As you suggested, I created a list 'Consumption Period' containing Month,Half Year, Quarter, Year.

Then I created an item subset LIS by choosing the items Last Month,Last Quarter, Half year and Year from the module

'Comp Consumption History'. 

Now, I am trying to map these two ( List and Item subset) and seem to be lost.

Can you please provide some lead here to complete the setup?

 

Thanks

Regards

Prabakaran

Highlighted
Regular Contributor

Re: Need help in setting the column

You do not need to use a LISS. 


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?

Good luck!

Highlighted
Frequent Contributor

Re: Need help in setting the column

@praba71,

 

Sorry for the delay in response.

 

Here I have attached the mock screens for your reference.

1) LIS mapping.PNG2) View.PNG3. LIS staging.PNG4 formula.PNG5 Final view.PNG

 

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.

 

 

Thanks,

Sandeep

 

Highlighted
Contributor

Re: Need help in setting the column

Thanks @ChrisHeathcote @sandeep_bk  for the detailed response .. 

Much appreciated..