topic Calculated rank field in back end script? in QlikView

Calculated rank field in back end script?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated rank field in back end script?

sunny_talwar — Thu, 06 Apr 2017 09:46:45 GMT

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];

Re: Calculated rank field in back end script?

sunny_talwar — Thu, 06 Apr 2017 09:57:51 GMT

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

Re: Calculated rank field in back end script?

devarasu07 — Thu, 06 Apr 2017 18:47:27 GMT

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

Re: Calculated rank field in back end script?

sunny_talwar — Thu, 06 Apr 2017 18:55:21 GMT

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?

Re: Calculated rank field in back end script?

devarasu07 — Thu, 06 Apr 2017 19:19:07 GMT

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

Re: Calculated rank field in back end script?

devarasu07 — Thu, 06 Apr 2017 19:27:56 GMT

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

Re: Calculated rank field in back end script?

sunny_talwar — Thu, 06 Apr 2017 19:28:43 GMT

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];