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

14 REPLIES 14
Highlighted
Regular 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
Super 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
Regular 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..

Highlighted
Contributor

Re: Need help in setting the column

@sandeep_bk  in  the screen shot showing the setup for staging consumption, you have used a list P1. Whats this list for?

 

Thanks

Regards

Prabakaran

Highlighted
Regular Contributor

Re: Need help in setting the column

@praba71,

 

I have used the P1 list just to represent the source module dimensions.

 

In your case, it could be an SKU list where you have past historical consumption quantity of a particular SKU under line items Cons_Last Month, Cons_Last Quarter, Cons_Last_Half Year, Cons_Last_Year.

 

I have created a staging module to stage the line items data into LIS and then maps to the final view with simple lookups.

 

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 details.

I am stuck up at setting up the staging consumption module.

Please see the screen shots.

1) I created a custom list 'Consumption Period' which has Month, Half year, Year and Quarter.

2) Then I created a Line item subset  'LIS' for my columns Last_Month,Last_Quarter,Half year and Year.

3) Then I created a mapping module'LIS Mapping'  to map 1 and 2.

4) Then I created a staging module 'Staging consumption' where I am trying to use the collect formula.

But this is where I am getting error.

 

Can you please suggest where I went wrong?

 

Thanks

Regards

Prabakaran

Highlighted
Regular Contributor

Re: Need help in setting the column

@praba71,

 

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.

 

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/COLLECT.html

 

Let me know if you still face any issues.

 

Thanks,

Sandeep