Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

kkkumar82
Valued Contributor III

Top sales calculation

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.

Tags (3)
1 Solution

Accepted Solutions
Highlighted
morganaaron
Valued Contributor

Re: Top sales calculation

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!

View solution in original post

4 Replies
Highlighted
morganaaron
Valued Contributor

Re: Top sales calculation

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!

View solution in original post

Highlighted
balkumarchandel
Valued Contributor II

Re: Top sales calculation

Can you post your example.

Highlighted
Not applicable

Re: Top sales calculation

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

Highlighted
kkkumar82
Valued Contributor III

Re: Top sales calculation

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.

KK