topic Re: Creating the same effect as "Dimension Limits" does with set analysis? in QlikView

Creating the same effect as "Dimension Limits" does with set analysis?

bwisealiahmad — Tue, 14 Jun 2016 13:57:52 GMT

Hi,

I have a line chart which looks like the following.

What I have done is I have a set analysis and on date it is max(numMonthYear-11)

Now I only have client for previous month, which is why it shows may 2016, but I want it to do so that it shows april 2016 as the last month up until the 20th of the month and then changes to may 2016.

My idea is to take a

IF statement and trim a left of the date so that I only get the day number and when it is over 20 I will have the expression for May 2016 month and if it is less it would use the expression which shows April 2016.

Now where I am confused is how do I make it highest month - 1?

The SET Expression is:

= Sum(

{$<

[Dato numMonthYear]={">=$(=Max([Dato numMonthYear])-11)"}

#Measure)

Re: Creating the same effect as "Dimension Limits" does with set analysis?

swuehl — Tue, 14 Jun 2016 14:12:10 GMT

Maybe something like

= Sum(

{$<

[Dato numMonthYear]={">=$(=Max([Dato numMonthYear])-11)<=$(=Max([Dato numMonthYear])-1)"}

#Measure)

= Sum(

{$<

[Dato numMonthYear]={">=$(=Max([Dato numMonthYear])-11)<=$(=Rangesum(Max([Dato numMonthYear]),If(Day(Today())>20,-1,-2)))"}

#Measure)

Re: Creating the same effect as "Dimension Limits" does with set analysis?

Digvijay_Singh — Tue, 14 Jun 2016 14:20:36 GMT

May be something like this -

= if Day(today(1))>=20,	Sum({$<[Dato numMonthYear]={">=$(=Max([Dato numMonthYear])-11)"}>}MeasuerField),

		Sum({$<[Dato numMonthYear]={">=$(=Max([Dato numMonthYear])-11),<=$(=Max([Dato numMonthYear]-1)"}>}MeasuerField)

Re: Creating the same effect as "Dimension Limits" does with set analysis?

bwisealiahmad — Tue, 14 Jun 2016 21:06:18 GMT

Thank you! Worked like a charm. I tried to put a -1 on the max before, but what does the last part actually do?

Re: Creating the same effect as "Dimension Limits" does with set analysis?

swuehl — Tue, 14 Jun 2016 21:09:14 GMT

Sorry, which part are you referring to?

This part

=Rangesum(Max([Dato numMonthYear]),If(Day(Today())>20,-1,-2))

should subtract 1 or 2 from the max numMonthYear depending on the current day of month.

(I put should in italic because I actually haven't tested the expression)

As far as I understood that's what you are trying to do?

Re: Creating the same effect as "Dimension Limits" does with set analysis?

bwisealiahmad — Tue, 14 Jun 2016 21:28:44 GMT

Yes, it worked fine, but I am thinking about this part:

<=$(=Max([Dato numMonthYear])-1)"}

In the first expresison which is the one I used and it worked perfectly. Is it saying between two ranges of the Date NumMonthYear or?

Re: Creating the same effect as "Dimension Limits" does with set analysis?

swuehl — Tue, 14 Jun 2016 21:35:01 GMT

{">=$(=Max([Dato numMonthYear])-11)<=$(=Max([Dato numMonthYear])-1)"}

The field modifier uses a numeric search expression and two dollar sign expansions which will be executed before the expression gets actually being evaluated (like a preprocessor text replacement):

If Max([Dato numMonthYear]) returns for example something like 42, the first dollar sign expansion will return 42-11 = 31 and the second 42-1 = 41, hence the expression will look like

= Sum(

{$<

[Dato numMonthYear]={">=31<=41"}

#Measure)

This numeric search will return all [Date numMonthYear] values in the range 31 to 41 inclusive and use these to filter the record set in the #Measure aggregation.

Re: Creating the same effect as "Dimension Limits" does with set analysis?

bwisealiahmad — Wed, 15 Jun 2016 18:07:55 GMT

Ok,

Thank you.