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.

1 Solution

Accepted Solutions
sunny_talwar

Sample image and qvw attached

Capture.PNG

View solution in original post

12 Replies
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

Sample image and qvw attached

Capture.PNG

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

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?

luciancostin
Contributor III
Contributor III
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' ?

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)

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

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?