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.

[Anonymous]

Sunny, i can not thank you enough for your support

however, it's not about my selection, and it doesn't work with distinct, or without it. First sorted value with rank 1 does return the current value, when i set it to 2, nothing.

[sunny_talwar]

But didn't you say this?

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.

[Anonymous]

stalwar1‌

I couldn't get it working directly as you mentioned it, and i'm not sure why, but your example file works and is doing exactly what i was looking for.

However, i had to tweak the formula on my end and manually include the selections, it looks as follows:

FirstSortedValue({1<report_date = {"$(='<=' & vSelectedReportDate )"}, year={$(vGlobalYear1)}, country=p(country), product=p(product) >}
Aggr(sum({1<report_date = {"$(='<=' & vSelectedReportDate )"}, year={$(vGlobalYear1)}, country=p(country), product=p(product)>} alt(marketing,0)), report_date, product)
, -Aggr(Only({1<report_date = {"$(='<=' & vSelectedReportDate )"}, year={$(vGlobalYear1)}, country=p(country), product=p(product)>} report_date),
report_date,
product),
2)

As far as i have checked, it yields the expected results. My formula includes other filters as i have them in my file

(product and country), as well as the year , which is yet another field in the products data table ( i had initially left this out for simplicity).