Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted

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)

View solution in original post

9 Replies
Highlighted

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)

View solution in original post

Highlighted

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

Does this look good?

Capture.PNG

Highlighted
Creator II
Creator II

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

too easy for you, master! stalwar1

Highlighted

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?

Highlighted
Creator II
Creator II

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?

Highlighted
Creator II
Creator II

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?

Highlighted

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)

Highlighted
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
Highlighted
Creator II
Creator II

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

I am agree! Thank you Jonathan!