Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have below two requirement
Please find the sample data as an attachment.
Thanks
Anu
Need to generate the Rank (First column) based on Total (Last column)
Rank(sum(Total))
Need to calculate the Sales % in each column based on the Dimensions MONTH and CATEGORY
You don't have a dimension MONTH so you need to create it first:
CrossTable(MONTH, Sales %,2)
LOAD
CATEGORY, Total, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCR, NOV, DEC
FROM
[Sample Data.xlsx] (ooxml, embedded labels, table is Sheet1)
;
Then you can use MONTH and CATEGORY as dimensions and sum([Sales %]) as expression.
Hi Anu,
For load your data:
CrossTable(Month, Data, 1)
LOAD CATEGORY,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCR,
NOV,
DEC
FROM
[Sample Data-1.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE UPPER(CATEGORY)<>'TOTAL';
For Sales%:
=If(Dimensionality()=1 and SecondaryDimensionality() = 1,sum(Data),
IF(Dimensionality()=0 and SecondaryDimensionality() = 1, sum(Data)/Sum(TOTAL <Month> Data),
IF(Dimensionality()=1 and SecondaryDimensionality() = 0, sum(Data)/Sum(TOTAL <CATEGORY> Data),
IF(Dimensionality()=0 and SecondaryDimensionality() = 1, sum(Data)/Sum(TOTAL <Month,CATEGORY> Data),
Sum(Data)/Sum(Total Data))
)))
For rank dim, add a calculated dimension:
=Aggr(Rank(TOTAL Sum(Data),2,0),CATEGORY)
Regards!!