4 Replies Latest reply: Feb 27, 2017 9:34 AM by Sunny Talwar

# Pivot table vs. expression (set analysis)

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

• ###### Re: Pivot table vs. expression (set analysis)

May be like this

Dimensions

Customer

Year

Expression

FirstSortedValue(Value, -MonthNum)

• ###### Re: Pivot table vs. expression (set analysis)

Sample qvw attached

• ###### Re: Pivot table vs. expression (set analysis)

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

• ###### Re: Pivot table vs. expression (set analysis)

Try this:

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

or

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