Display only values of a field depending on the max existing value of another field

[caccio88]

Hi guys,

what I would like to do is to create a pivot table.

"Actual" values ( where we have value from jan to may) and "Budget" values ( where we have value from jan to Dec).

I would like that the value of budget changes depending on the max existing value of month of "Actual".

This is what i have now. Where values of "Actual" are from jan to may and values of "Budget" and "Y-1" are from jan to dec.

What i would like is that budget and y-1 change their values depending on the max existing value of month that we have in "Actual".

Is that possible in the expression of the table?

This is what i would like to have (filtering on month):

Is that clear enough?

Here qvw and xls attached.

Thanks.

Filiberto

stalwar1‌ swuehl‌ gwassenaar‌ cleveranjos‌

[sunny_talwar · Accepted solution]

May be using this:

Sum({<YearMonthID ={"<=$(=Max({<Scenario = {'Actual'}>}YearMonthID))"}, [Scenario]={'Budget'} >} Value)

[sunny_talwar]

Does this look good?

[caccio88]

too easy for you, master! stalwar1‌

[sunny_talwar]

With regards to Y-1, what exactly is that covering?

[caccio88]

It is the LY Actual. So we have value from jan 2015 to dec 2015.

Understand?

[caccio88]

maybe this: Sum({<YearMonthID ={"<=$(=Max({<Scenario = {'Actual'}>}YearMonthID)-12)"}, [Scenario]={'Actual'}, [DataYear]={'2015'}>} Value)

What do you think?

[sunny_talwar]

I think this should work:

Sum({<[Scenario]={'Actual'}, [DataYear]={'2015'}>} Value)

[jonathandienst]

The Y-1 expression should read:

Sum({<

  YearMonthID ={"<=$(=Max({<Scenario = {'Actual'}>}YearMonthID) - 12)"},

  [Scenario]={'Actual'},

  [DataYear]={'2015'}

>} Value)

This returns the correct result of 22 790 535.

Edit - beaten to the punch by caccio88

[caccio88]

I am agree! Thank you Jonathan!