Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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.
Hi,
You have to create a variable :
vPeendtnum=Max({1}peendtnum)
Then your formula will be:
=sum({<peendtnum={"$(=(vPeendtnum))"}>} hrsbudmth)
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!
Do not create the variable in the script, once you are on the sheet, you can create it with CTRL+ALT+V
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)
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.
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?!?
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