topic Re: Display only values of a field depending on the max existing value of another field in QlikView

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

caccio88 — Wed, 15 Jun 2016 13:03:03 GMT

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‌

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

sunny_talwar — Wed, 15 Jun 2016 13:09:27 GMT

May be using this:

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

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

sunny_talwar — Wed, 15 Jun 2016 13:11:10 GMT

Does this look good?

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

caccio88 — Wed, 15 Jun 2016 13:12:15 GMT

too easy for you, master! stalwar1‌

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

sunny_talwar — Wed, 15 Jun 2016 13:14:18 GMT

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

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

caccio88 — Wed, 15 Jun 2016 13:15:41 GMT

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

Understand?

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

caccio88 — Wed, 15 Jun 2016 13:17:16 GMT

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

What do you think?

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

sunny_talwar — Wed, 15 Jun 2016 13:18:50 GMT

I think this should work:

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

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

jonathandienst — Wed, 15 Jun 2016 13:18:55 GMT

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

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

caccio88 — Wed, 15 Jun 2016 13:21:36 GMT

I am agree! Thank you Jonathan!