Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anukumari
Contributor II
Contributor II

Calculation of Rank and Share Percentage

I have below two requirement

  1. Need to generate the Rank (First column) based on Total (Last column)
  2. Need to calculate the Sales % in each column based on the Dimensions MONTH and CATEGORY

Please find the sample data as an attachment.

Thanks

Anu

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable

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!!