Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
i have researched various Qlikview platforms and books but can't find out what I am doing wrong. I have a text field where I would like to show my YtD sales. This is the formula I use:
=sum({<Year={$(Year(Max (DATETIME)))},DATETIME={'<=$(Max (DATETIME))'},Month=, Day=,Hour=,Week=,Weekday=>}PRICE)
It does not give any data back or an error. So I have no clue where to start.
Can anybody help me?
Best regards,
Dorine
Hi,
Try this
Current Year YTD
=sum({<DATETIME={'<=$Date(Max (DATETIME)))>=$(=Date(YearStart(max(DATETIME))))'},Month=>}PRICE)
Last Year YTD
=sum({<DATETIME={'<=$Date(addYears(Max (DATETIME),-1)))>=$(=Date(YearStart(max(DATETIME),-1)))'},Year=>}PRICE)
Or You can try this
Current Year YTD
=sum({<DATETIME={'<=$Timestamp(Max (DATETIME)))>=$(=Timestamp(Date(YearStart(max(DATETIME)))))'},Month=>}PRICE)
Last Year YTD
=sum({<DATETIME={'<=$Timestamp(Date(addYears(Max (DATETIME),-1))))>=$(=Timestamp(Date(YearStart(max(DATETIME),-1))))'},Year=>}PRICE)
Hi Dorine,
A bit of syntax:
=sum({<Year={$(=Year(Max (DATETIME)))}, DATETIME={'<=$(=Date(Max(DATETIME)))'},Month=, Day=,Hour=,Week=,Weekday=>}PRICE)
Note that if you have already selected the maximum datetime, then you need not to select the highest year.
On the other hand, if DATETIME is a date field, not a numeric field, then you need to convert it using the Date() function.
Miguel
Hi,
Try this
Current Year YTD
=sum({<DATETIME={'<=$Date(Max (DATETIME)))>=$(=Date(YearStart(max(DATETIME))))'},Month=>}PRICE)
Last Year YTD
=sum({<DATETIME={'<=$Date(addYears(Max (DATETIME),-1)))>=$(=Date(YearStart(max(DATETIME),-1)))'},Year=>}PRICE)
Or You can try this
Current Year YTD
=sum({<DATETIME={'<=$Timestamp(Max (DATETIME)))>=$(=Timestamp(Date(YearStart(max(DATETIME)))))'},Month=>}PRICE)
Last Year YTD
=sum({<DATETIME={'<=$Timestamp(Date(addYears(Max (DATETIME),-1))))>=$(=Timestamp(Date(YearStart(max(DATETIME),-1))))'},Year=>}PRICE)
Thanks Miguel for your feedback.
DATETIME is a numeric timestamp field.
I copy paste your code but it doesn't work.
Any other ideas?
If the code is correct, then
=sum({1<Year={$(=Max(Year(DATETIME)))}, DATETIME={'<=$(=Date(Max(DATETIME)))'},Month=, Day=,Hour=,Week=,Weekday=>}PRICE)
Miguel
HI Max,
Your second option works!!!!!! Thanks!!!!
Best regards,
Dorine
One more question Max, your second option works perfect for the current YtD. Last year YtD however, gives the sum of 2013 and 2014 in stead of only 2013.
Any idea of what I am doing wrong?
Hi,
don't nullify Year field.
Like this
=sum({<DATETIME={'<=$Timestamp(Date(addYears(Max (DATETIME),-1))))>=$(=Timestamp(Date(YearStart(max(DATETIME),-1))))'}>}PRICE)