Pivot table vs. expression (set analysis)

[Anonymous]

Hi all,

I hope someone can help.

Below an example dataset:

Customer 1	2016	1	100
Customer 1	2016	2	200
Customer 1	2016	3	300
Customer 1	2016	4	400
Customer 1	2016	5	500
Customer 1	2016	6	600
Customer 1	2016	7	700
Customer 1	2016	8	800
Customer 1	2016	9	900
Customer 1	2016	10	1000
Customer 1	2016	11	1100
Customer 1	2016	12	1200
Customer 1	2017	1	125
Customer 1	2017	2	250
Customer 2	2016	1	250
Customer 2	2016	2	500
Customer 2	2016	3	750
Customer 2	2016	4	1000
Customer 2	2016	5	1250
Customer 2	2016	6	1500
Customer 2	2016	7	1750
Customer 2	2016	8	2000
Customer 2	2016	9	2250
Customer 2	2016	10	2500
Customer 2	2016	11	2750
Customer 2	2016	12	3000
Customer 2	2017	1	275
Customer 2	2017	2	550

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

When i select no months the pivot table must show 

Years	2016	2017
Customer 1	1200	250
Customer 2	3000	550

When i select month 1 the pivot table must show 

Years	2016	2017
Customer 1	100	125
Customer 2	250	275

Best regards,

Arjan

[sunny_talwar]

May be like this

Dimensions

Customer

Year

Expression

FirstSortedValue(Value, -MonthNum)

[sunny_talwar]

Sample qvw attached

[Anonymous]

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))