Set analysis max value smaller than

[Anonymous]

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.

[sunny_talwar]

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))

[sunny_talwar]

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)

[sunny_talwar · Accepted solution]

Sample image and qvw attached

[Anonymous]

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

[sunny_talwar]

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]

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' ?

[sunny_talwar]

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]

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.

[sunny_talwar]

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