Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luciancostin
Contributor III
Contributor III

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.

12 Replies
luciancostin
Contributor III
Contributor III
Author

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.
luciancostin
Contributor III
Contributor III
Author

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