Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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, 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.
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.
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).