Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Set analysis max value smaller than

Hello dear Qlikview community,

I'm struggling with no luck with the following problem, perhaps you could give me a hint:

I need to sum up all values pertaining to the maximum reported date, but smaller than another given value, in a Pivot Table.

That is,

- we have the current date: let's say 201806 (yyyymm)

- we have the chart with dimension Product

- for each product, there are multiple series of sales values, pertaining to different reported dates, say: 201804, 201801, 201711 (not all products have reported for a given date! )

Example:

Products :

A

B

Sales (report_date, product, sales):

201806  A   50

201806  A   30

201804  A   20

201804  A   30

201806  B   20

201806  B   20

201801  B   20

201801  B   15

I'm looking for:

sum( {1< report_date={Greatest_YYYMM_date_but_Smaller_than_current_selection}  >} sales )

I've tried many variants along the lines:

sum( {1< report_date={ `= \$(= max({1< report_date={"\$(= '<'& vSelectedSubmission)"} >} report_date )  )` }  >} sales)

How can i get this working, what am i missing?

The result should be somewhat like:

product     previous_report     current_report

A               50                   80

B               35                   40

Thank you very much for any ideas.

1 Solution

Accepted Solutions
MVP

Sample image and qvw attached

12 Replies
MVP

May be like this

FirstSortedValue({<report_date = {"\$(='<=' & Max(report_date))"}>} Aggr(Sum({<report_date = {"\$(='<=' & Max(report_date))"}>} sales), report_date, product), -Aggr(Only({<report_date = {"\$(='<=' & Max(report_date))"}>} report_date), report_date, product))

MVP

For previous

FirstSortedValue({<report_date = {"\$(='<=' & Max(report_date))"}>} Aggr(Sum({<report_date = {"\$(='<=' & Max(report_date))"}>} sales), report_date, product), -Aggr(Only({<report_date = {"\$(='<=' & Max(report_date))"}>} report_date), report_date, product), 2)

MVP

Sample image and qvw attached

Anonymous
Not applicable
Author

hi Sunny and thank you very much for your so quick answer.

Your script does exactly what i'm looking for,

However, for some reason, when i port it in my application, it doesn't yield the desired results anymore .. still trying around your answer

MVP
 it doesn't yield the desired results anymore ..

Does it yield any result? Do you know what might it be doing incorrectly? Difficult to know unless you are able to share more details or a sample?

Anonymous
Not applicable
Author

It doesn't yield any results.

If i use 1 as rank for the FirstSortedValue parameter, i do get the current values, if i use 2, to retrieve the second record, nothing, nowhere comes up.

Btw, can you explain why using 'only' and not for example 'max' ?

MVP

Try adding a DISTINCT after FirstSortedValue()

FirstSortedValue(DISTINCT {<report_date = {"\$(='<=' & Max(report_date))"}>} Aggr(Sum({<report_date = {"\$(='<=' & Max(report_date))"}>} sales), report_date, product), -Aggr(Only({<report_date = {"\$(='<=' & Max(report_date))"}>} report_date), report_date, product), 2)

Anonymous
Not applicable
Author

Ah, ok i found the reason why it does not yield anything, though i did not also find the solution

I have a filter for report_date. The report_date is being selected, that's what i need for the 'current report date', let's call it a 'reference date', hence the 'previous date' is whatever is earlier than this selection.

MVP

I did add a set analysis to handle this problem... but it might not be working... what is the format for report_date?

Community Browser