Set analysis to retrieve YtD total

[#1]

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

[Miguel_Angel_Baeyens]

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

[PrashantSangle · Accepted solution]

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)

[#4]

Thanks Miguel for your feedback.

DATETIME is a numeric timestamp field.

I copy paste your code but it doesn't work.

Any other ideas?

[Miguel_Angel_Baeyens]

If the code is correct, then

=sum({1<Year={$(=Max(Year(DATETIME)))}, DATETIME={'<=$(=Date(Max(DATETIME)))'},Month=, Day=,Hour=,Week=,Weekday=>}PRICE)

Miguel

[#6]

HI Max,

Your second option works!!!!!! Thanks!!!!

Best regards,

Dorine

[#7]

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?

[PrashantSangle]

Hi,

don't nullify Year field.

Like this

=sum({<DATETIME={'<=$Timestamp(Date(addYears(Max (DATETIME),-1))))>=$(=Timestamp(Date(YearStart(max(DATETIME),-1))))'}>}PRICE)