Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
fheidenstecker
Contributor II

Sales of minimum and maximum month

Hello Qlik Community,

i have got a question regarding sales of min and max month for each customer.

I want to show the the sales of the minimum month and the maximum month in a table. i Created some sample data

 

CustomerDateSales
A01.01.2015374
A01.02.2015121
A01.03.2015318
A01.04.2015400
B01.02.2015179
B01.03.2015440
B01.04.2015349
C01.01.2015332
C01.02.2015496
C01.03.2015107
D01.02.2015121
D01.03.2015183
E01.03.2015

222

So for Customer A Min Month is January and Max month is April.

For Customer B Min Month is February and Max month is April

And so on.

I used the following expression in QlikView

sum(if(MonatId=aggr(min(MonatId),Customer),Sales))

It seems to work for minimum values, but for maximim values it shows only the sales of Customer E. Any idea why?

The result i would expect would be

Customer
Min Month Sales
Max Month Sales
A374400
B179349
C332107
D121183
E222222

I attached a sample file.

I really could use some help 🙂

Regards from Germany,

Fabian

1 Solution

Accepted Solutions
MVP
MVP

Re: Sales of minimum and maximum month

Min

FirstSortedValue(Aggr(SUM(Sales),Customer,Monat),Monat)

Max

FirstSortedValue(Aggr(SUM(Sales),Customer,Monat),-Monat)

View solution in original post

3 Replies
MVP
MVP

Re: Sales of minimum and maximum month

Min

FirstSortedValue(Aggr(SUM(Sales),Customer,Monat),Monat)

Max

FirstSortedValue(Aggr(SUM(Sales),Customer,Monat),-Monat)

View solution in original post

Re: Sales of minimum and maximum month

Try with FirstSortedValue function

PFA

fheidenstecker
Contributor II

Re: Sales of minimum and maximum month

It works! Thanks!