Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
caccio88
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
sunny_talwar

May be using this:

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

View solution in original post

9 Replies
sunny_talwar

May be using this:

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

sunny_talwar

Does this look good?

Capture.PNG

caccio88
Creator II
Creator II
Author

too easy for you, master! stalwar1

sunny_talwar

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

caccio88
Creator II
Creator II
Author

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

Understand?

caccio88
Creator II
Creator II
Author

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
Partner - Champion III
Partner - Champion III

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

I am agree! Thank you Jonathan!