Need help in setting the column

Sorry for the delay in response.

Here I have attached the mock screens for your reference.

1) LIS mapping.PNG 2) View.PNG 3. LIS staging.PNG 4 formula.PNG 5 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 @ChrisAHeathcote 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

All comments

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

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

Screen_Shot_2020-09-18_at_11_84374.png

ChrisAHeathcote

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!

Sorry for the delay in response.

Here I have attached the mock screens for your reference.

1) LIS mapping.PNG 2) View.PNG 3. LIS staging.PNG 4 formula.PNG 5 Final view.PNG

Please note: your set up might slightly vary based on your dimensions and other non-accountable components. However, this will help.

Thanks,

Sandeep

Thanks @ChrisAHeathcote @sandeep_bk for the detailed response ..

Much appreciated..

@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

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

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

Screen_Shot_2020-09-25_at_12_85103.png

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

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.

Let me know if you still face any issues.

Thanks,

Sandeep

Thanks @sandeep_bk .. I will check this one out.

Regards

Prabakaran

Hello @sandeep_bk

Thanks a lot for the details.. But, I am still missing something, it seems, Please see the screen shots attached, after the setups, in the target module, I dont see the Consumption column showing the subset values. It is still showing as one column. Would appreciate if we can connect in a short google hangout call to sort this out, if that is possible.

Again, thanks for your time.

Regards

Prabakaran

Screen_Shot_2020-09-28_at_12_85316.png

Here is my LinkedIn Profile https://www.linkedin.com/in/mesandee/.

Add me as your connection. Let's connect over there.

Thanks,

Sandeep

Thanks @sandeep_bk .. Sent you the request.

Regards

Prabakaran