topic Re: Top sales calculation in QlikView

Top sales calculation

kkkumar82 — Tue, 25 Nov 2014 10:04:11 GMT

Hi all,

I have used the following expression

='The spend and savings of ' &GetFieldSelections(Supplier)&' is '& sum(Spend)&', '&sum([MPN Savings])&'

'&'Top Spend is '& firstsortedvalue(Supplier, -Aggr(sum({1}[Spend]),Commodity,Supplier))&'

'&max(aggr(sum({1}Spend),Commodity,Supplier))

in a text box to show the selected supplier and top supplier spend and it is fine when nothing is selected but when for eg

a supplier is selected the selected and top supplier are the same, to avoid this I have also applied the identifier {1} but not working.

Do I need to calculate the top supplier in the edit script or is there any alternative the front end.

Regards

KK.

morganaaron — Tue, 25 Nov 2014 10:14:35 GMT

Hi Kiran,

I think you're simply missing a few {1)s, try inserting:

firstsortedvalue({1}Supplier..

and

Max({1}Aggr(..

and it should work!

Anonymous — Tue, 25 Nov 2014 10:16:39 GMT

Can you post your example.

Tue, 25 Nov 2014 10:21:39 GMT

I think you are using the wrong modifier. try $ instead of {1} because $ is the current selection.

kkkumar82 — Wed, 26 Nov 2014 10:48:21 GMT

Hi Aaron for the above data set , if I want to know the top three suppliers we can use concat along with aggr,

I know it, but if I want to know the sum of spend of top three suppliers and average of spend of top three suppliers

in a text box , how to do it.

Note: here I have indirect set analysis i.e. the average i cannot calculate directly by having sum(spend)/3 because

for a commodity I may not have three suppliers sometimes.