Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivot table vs. expression (set analysis)

Hi all,

I hope someone can help.

Below an example dataset:

Customer 120161100
Customer 120162200
Customer 120163300
Customer 120164400
Customer 120165500
Customer 120166600
Customer 120167700
Customer 120168800
Customer 120169900
Customer 12016101000
Customer 12016111100
Customer 12016121200
Customer 120171125
Customer 1

2017

2

250

Customer 220161250
Customer 220162500
Customer 220163750
Customer 2201641000
Customer 2201651250
Customer 2201661500
Customer 2201671750
Customer 2201682000
Customer 2201692250
Customer 22016102500
Customer 22016112750
Customer 22016123000
Customer 220171275
Customer 220172

550

Now, i want to use an pivot table with the folowing functionality:

When i select no months the pivot table must show 

Years20162017
Customer 11200250
Customer 23000

550

When i select month 1 the pivot table must show 

Years20162017
Customer 1100125
Customer 2250275

Best regards,

Arjan

4 Replies
sunny_talwar

May be like this

Dimensions

Customer

Year

Expression

FirstSortedValue(Value, -MonthNum)

sunny_talwar

Sample qvw attached

Anonymous
Not applicable
Author

Hi Sunny,

Thanks for your respons. Its almost right!

I have in de dataset more records for the same period and customer. Then it is not working.

Do I need to putt a sum in the expression?

Best regards,

Arjan

sunny_talwar

Try this:

FirstSortedValue(Aggr(Sum(Value), Customer, Year, MonthNum), -MonthNum)

or

FirstSortedValue(Aggr(Sum(Value), Customer, Year, MonthNum), -Aggr(MonthNum, Customer, Year, MonthNum))