Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
Can someone correct me on this below one.
I have 2 dimensions Month and MDT_Type and
I have been trying to achieve the previous month sales to build some business calculation.
But it didn't work properly.
Can someone correct on this below expression
Above(total Sum(NetSales),Count(Total distinct MDT_Type)).
This needs to be done at a data model level, any solution using above() on the chart level will be based completely on the sorting order of the dimensions and change in sorting will return incorrect values
below is model level solution
Raw:
Load date#(month,'MMM-YY') as month,mdt_type,Sales,mdt_type&'-'&date#(month,'MMM-YY') as keyfield Inline [
month,mdt_type,Sales
Oct-21,A,10
Oct-21,B,20
Oct-21,C,30
Nov-21,A,40
Nov-21,B,50
Dec-21,A,70
Dec-21,B,80
Dec-21,C,90
Jan-22,B,70
Jan-22,C,80
Jan-22,D,90
];
MDT_month:
Load
keyfield
,month as month2
,mdt_type as mdt_type2
,'currentmonth' as type
Resident Raw;
Load
mdt_type&'-'&date(addmonths(date#(month,'MMM-YY'),-1),'MMM-YY') as keyfield
,month as month2
,mdt_type as mdt_type2
,'prevmonth' as type
Resident Raw;
output
Thanks for quick solution.
what if I have too many dimensions to be used.
I.e those dimensions will change dynamically based upon my selections.
This time I have mtd_type but if switch to different dimension, Then
do I need to create this keyfield based on my dimension?
no you only need a keyfield on the lowest granular level, and load those dimensions in the intermediate table
ie MDT_month and use the dimensions from this table
kindly close this thread by marking a response as a "Solution"