Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

caccio88
New Contributor III

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

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

stalwar1swuehlgwassenaarcleveranjos

1 Solution

Accepted Solutions

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

May be using this:

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

9 Replies

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

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

Does this look good?

Capture.PNG

caccio88
New Contributor III

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

too easy for you, master! stalwar1

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

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

caccio88
New Contributor III

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

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

Understand?

caccio88
New Contributor III

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

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

I think this should work:

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

MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
caccio88
New Contributor III

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

I am agree! Thank you Jonathan!

Community Browser