topic Re: Total Peak sold? in QlikView

Total Peak sold?

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

Hi All,

Can you advise me on below issue,

Used below set analysis in expression

=Sum(Aggr(max({<Week_No=,Week_No,Batch,ArticleNo>}

aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,Batch,ArticleNo)),ArticleNo,Batch))

FYI, refer the expected output screen image

Tks, Dev

Re: Total Peak sold?

sunny_talwar — Tue, 06 Dec 2016 10:42:33 GMT

Try this:

=If(Dimensionality() = 0, Max(Aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,ArticleNo)),

max({<Week_No=,Week_No,Batch,ArticleNo>}

aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,Batch,ArticleNo)))

Re: Total Peak sold?

devarasu07 — Tue, 06 Dec 2016 10:45:51 GMT

Hi Sunny,

Thank you so much

Re: Total Peak sold?

sunny_talwar — Tue, 06 Dec 2016 10:46:11 GMT

No problem

Re: Total Peak sold?

devarasu07 — Wed, 07 Dec 2016 07:33:28 GMT

Hi Sunny,

Above solution is working fine, but if we add week filter then it's not giving right total. kindly advise. Thanks

=If(Dimensionality() = 0, Max(Aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,ArticleNo)),

max({<Week_No=,Week_No,Batch,ArticleNo>}

aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,Batch,ArticleNo)))

Re: Total Peak sold?

sunny_talwar — Wed, 07 Dec 2016 09:31:23 GMT

Try this:

=If(Dimensionality() = 0, Max({<Week_No=,Week_No,ArticleNo>}Aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,ArticleNo)),

max({<Week_No=,Week_No,Batch,ArticleNo>}

aggr(sum({<Week_No=,Week_No,ArticleNo>}Sold_Qty),Week_No,Batch,ArticleNo)))

Re: Total Peak sold?

devarasu07 — Wed, 07 Dec 2016 10:17:33 GMT

Hi Sunny,

PFA, If we change to other week number from the filter then Peak sold value & Total not changing. is it possible to get the Peak sold value & total based on dynamic week selection from filter. kindly advise. Thank you

Re: Total Peak sold?

sunny_talwar — Wed, 07 Dec 2016 10:23:52 GMT

Here you go:

=If(Dimensionality() = 0, Max({<Week_No= {"$(='<=' & Max(Week_No))"},ArticleNo>}Aggr(sum({<Week_No,ArticleNo>}Sold_Qty),ArticleNo, Week_No)),

max({<Week_No= {"$(='<=' & Max(Week_No))"},Batch,ArticleNo>}

aggr(sum({<Week_No= {"$(='<=' & Max(Week_No))"},ArticleNo>}Sold_Qty),Week_No,Batch,ArticleNo)))