topic Re: Set analysis in QlikView

Set analysis

Tue, 05 Mar 2013 14:21:44 GMT

Hi,

I am new to qlikview and I am having difficulty in creating an expression in giving me the sum of "hrsbudmth" for the latest period only regardless if another period is selected

My period has a date attached which is in dd/mm/yyyy format.

At present my expression is:

=sum({<peendtnum = {$(=max(peendtnum))}>} hrsbudmth)

I am converting the date to a number in my load statement hence the name peendtnum.

I have looked at other set analysis blogs but cannot work out why my expression is changing if I select another period?

Any help would be appreciated!

Re: Set analysis

Gysbert_Wassenaar — Tue, 05 Mar 2013 14:26:14 GMT

There may be other fields influencing the possible max value. To make the expression ignore all other expressions use: =sum({1<peendtnum = {$(=max({1}peendtnum))}>} hrsbudmth)

If you want to ignore selections in specific fields use something like:

=sum({<MyField1=, MyField2=, peendtnum = {$(=max({1}peendtnum))}>} hrsbudmth)

Re: Set analysis

Tue, 05 Mar 2013 14:35:50 GMT

Thanks for the quick reply Gysbert.

value 0213 = 100.00

value 0113 = 50.00

I have tried your formula:

=sum({1<peendtnum = {$(=max(peendtnum))}>} hrsbudmth)

When I do not select a period from my period list box the value in my box (Current Month) shows the amount (100.00) for the lastest period (0213), however when I select period 0113 the amount changes to the value relating to 0113 (50.00) when I want this to stay as 100.00.

As I have another box called (Previous month) which is where the 50.00 should appear.

Re: Set analysis

Anonymous — Tue, 05 Mar 2013 15:14:49 GMT

Hi,

You have to create a variable :

vPeendtnum=Max({1}peendtnum)

Then your formula will be:

=sum({<peendtnum={"$(=(vPeendtnum))"}>} hrsbudmth)

Re: Set analysis

Tue, 05 Mar 2013 15:53:54 GMT

I've just tried to create the variable within the load statement but the variable comes back with nothing?

LOAD pe,

peendt,

num(peendt) as peendtnum;

SQl seelct ........

SET vPeendtnum=Max({1}peendtnum);

I'm getting very confused now!

Re: Set analysis

Anonymous — Tue, 05 Mar 2013 16:32:02 GMT

Do not create the variable in the script, once you are on the sheet, you can create it with CTRL+ALT+V

Re: Set analysis

Tue, 05 Mar 2013 16:56:48 GMT

Thanks Bobby!

I've now created the variable as per your previous post. Just one final question, if I wanted to go back one month could I take the variable I have created but add -35?

so it would read

=sum({peendt=,pe=,peendtnum={$(=(vPeendtnum-35))}>} hrsbudmth)

Re: Set analysis

Anonymous — Tue, 05 Mar 2013 17:04:37 GMT

Yes you can but why -35 ???

You should add -30 or -31 depending on the month.

Actually, you have to add the number of days you want to go back from your variable.

Re: Set analysis

Wed, 06 Mar 2013 10:41:51 GMT

Sorry yes you are correct bobbyraj_sg it should be -30 or -31 (unless Feb when its -28).

I now have another query. If I'm coding the dates as numbers so Jan is 41305 and Feb is 41333 (difference of 28).

I need some clever trick where in my formula for Jan (Previous month) instead of having to manually change the -28 each month dependent on the number of days (either -30 or -31) from month to month to be a set expression.

=sum({<peendt=,pe=,peendtnum={$(=(vPeendtnum-28))

Effectively I think I need to create another variable but able to look at the 2nd max month!

=Max({1}peendtnum)

I hope this makes sense?!?

Re: Set analysis

Anonymous — Wed, 06 Mar 2013 11:10:15 GMT

You can use fonctions like addmonths to add or remove the number of month you want to a given date. Then you can convert it to num.

Hope this can help you