Calculated rank field in back end script?

[devarasu07]

Hi All,

How to create Rank for below calculated field (GrossPrice$, NetPrice$, Markdown%) in Back end script method. because based on this rank field i need to sort my Report in front end.

Tried like below script but rank not working properly.

[Sales Aggr]:

load *, AutoNumber(Markdown%) as Markdown_Rank;

load distinct

  SalesArticleNo,

    ((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) as GrossPrice$,

    if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) ,((sum(TotalNetPriceSGD)*1.07)/sum(SoldQty))) as NetPrice$,

    if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,0 , ((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD))/sum(TotalGrossPriceSGD))) as Markdown%

Resident DailySalesFact group by SalesArticleNo order by Markdown_Rank desc;

My env.

Qlik v11.2 version (in my version Sort able aggr feature wont work) so need to do in back end.

Thanks,

Deva

[sunny_talwar]

This Rank will be a static rank and will not change based on selection? Is that what you are looking for? May be this

[Sales Aggr]:

LOAD SalesArticleNo,

  ((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) as GrossPrice$,

  if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) ,((sum(TotalNetPriceSGD)*1.07)/sum(SoldQty))) as NetPrice$,

  if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,0 , ((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD))/sum(TotalGrossPriceSGD))) as Markdown%

  Resident DailySalesFact

  group by SalesArticleNo;

FinalAggr:

LOAD SalesArticleNo,

    Markdown%,

    RowNo() as Rank

Resident [Sales Aggr]

Order By Markdown% desc;

DROP Table [Sales Aggr];

[sunny_talwar]

And the other thing is that the Rank function isn't dependent on sorting. Rank just depends on how big or small your measure is based on any sorting order.... are you confusing Rank with something like RangeSum(Above()) may be? Because RangeSum(Above()) is sort dependent....

[devarasu07]

Hi Sunny,

Thanks for your help. I've but still not getting the expected order and output.

Please refer to attached document. It's Nprint purpose only and no front end filter selection on it.

[Sales Aggr]:

LOAD SalesBarcode,

  ((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) as GrossPrice$,

  if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) ,((sum(TotalNetPriceSGD)*1.07)/sum(SoldQty))) as NetPrice$,

  (if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,0 , ((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD))/sum(TotalGrossPriceSGD))))*100 as Markdown%

  Resident DailySalesFact

group by SalesBarcode;

[Markdown Rank]:

LOAD SalesBarcode,

    Markdown%,

    RowNo() as Rank

Resident [Sales Aggr]

Order By  Markdown%; // (here need to add two more sort field) Group by Article,Batch Order by latest launch_ID asc, Markdown% desc,SoldQty desc

Drop table [Sales Aggr];

Table Grain:

Lowest level is Barcode which is Batch (i.e Article Color)

Looking for your valuable suggestion on this

Regards,

Deva

[sunny_talwar]

Are you saying that Sales Aggr() will be doing Aggregation on multiple dimensions and not just SalesBarcode?

[Sales Aggr]:

LOAD Article,

          [Batch Order],

     SalesBarcode,

  ((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) as GrossPrice$,

  if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) ,((sum(TotalNetPriceSGD)*1.07)/sum(SoldQty))) as NetPrice$,

  (if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,0 , ((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD))/sum(TotalGrossPriceSGD))))*100 as Markdown%

  Resident DailySalesFact

group by Article, [Batch Order], SalesBarcode;

But since they are higher grain, you did not add it? I would suggest you to add it... Now the Rank will restart for each every Article and Batch Order? Is that what the requirement is?

[devarasu07]

Hi Sunny,

Thanks, Please refer to the attached report layout and mock value.

back end script need to get Sortable aggr. rank value for Markdown% or Netprice $ then need to show attached report based on below order .

Rank (Qty) : Group by Launch, ArticleNo,Batch Order By Launch_ID asc (latest launch to old), Markdown%  or NetPrice $ desc ,SoldQty Desc

Thanks,

Deva

[devarasu07]

Hi,

Are you saying that Sales Aggr() will be doing Aggregation on multiple dimensions and not just SalesBarcode?

Yeah Multiple dimension

1) Launch_ID (asc order)

2) SalesBarcode (actually report need to show ArticleNo, Batch)

3) Markdown % or NetPrice$ (Desc order)

aggr(Rank(sum(SoldQty),Launch_ID,Markdown%_Rank ArticleNo)

But since they are higher grain, you did not add it? I would suggest you to add it... Now the Rank will restart for each every Article and Batch Order? Is that what the requirement is?

I think my model not good. Materailmaster dimension table has ArticleNo,Barcode details but Fact table has only barcode level.

Thanks,

Deva

[sunny_talwar]

From what I understand, you need this

[Sales Aggr]:

LOAD Launch,

          Material,

  ((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) as GrossPrice$,

  if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,((sum(TotalGrossPriceSGD)*1.07)/sum(SoldQty)) ,((sum(TotalNetPriceSGD)*1.07)/sum(SoldQty))) as NetPrice$,

  (if(((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD)) / sum(TotalGrossPriceSGD)) < 0.2 ,0 , ((sum(TotalGrossPriceSGD) - sum(TotalNetPriceSGD))/sum(TotalGrossPriceSGD))))*100 as Markdown%

  Resident DailySalesFact

group by Launch, Material;

FinalAggr:

LOAD *,

    If(Launch = Previous(Launch) and Material = Previous(Material), RangeSum(Peek('Rank'), 1), 1) as Rank

Resident [Sales Aggr]

Order By Launch, Material, Markdown% desc;

DROP Table [Sales Aggr];