topic Re: Set analysis max value smaller than in QlikView

Set analysis max value smaller than

Anonymous — Tue, 15 May 2018 17:22:14 GMT

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 :

Sales (report_date, product, sales):

201806 A 50

201806 A 30

201804 A 20

201804 A 30

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.

Re: Set analysis max value smaller than

sunny_talwar — Tue, 15 May 2018 17:27:20 GMT

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

Re: Set analysis max value smaller than

sunny_talwar — Tue, 15 May 2018 17:27:33 GMT

For previous

Re: Set analysis max value smaller than

sunny_talwar — Tue, 15 May 2018 17:43:08 GMT

Sample image and qvw attached

Re: Set analysis max value smaller than

Anonymous — Wed, 16 May 2018 12:18:18 GMT

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

Re: Set analysis max value smaller than

sunny_talwar — Wed, 16 May 2018 12:20:19 GMT

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?

Re: Set analysis max value smaller than

Anonymous — Wed, 16 May 2018 13:14:11 GMT

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

Re: Set analysis max value smaller than

sunny_talwar — Wed, 16 May 2018 13:16:28 GMT

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)

Re: Set analysis max value smaller than

Anonymous — Wed, 16 May 2018 13:18:25 GMT

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.

Re: Set analysis max value smaller than

sunny_talwar — Wed, 16 May 2018 13:20:28 GMT

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

Re: Set analysis max value smaller than

Anonymous — Wed, 16 May 2018 15:34:51 GMT

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.

Re: Set analysis max value smaller than

sunny_talwar — Wed, 16 May 2018 16:03:40 GMT

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.

Re: Set analysis max value smaller than

Anonymous — Wed, 23 May 2018 11:36:01 GMT

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