topic Re: Set analysis to retrieve YtD total in QlikView

Set analysis to retrieve YtD total

Sat, 15 Mar 2014 09:46:13 GMT

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

Re: Set analysis to retrieve YtD total

Miguel_Angel_Baeyens — Sat, 15 Mar 2014 09:51:07 GMT

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

Re: Set analysis to retrieve YtD total

PrashantSangle — Sat, 15 Mar 2014 09:53:19 GMT

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)

Re: Set analysis to retrieve YtD total

Sat, 15 Mar 2014 10:45:24 GMT

Thanks Miguel for your feedback.

DATETIME is a numeric timestamp field.

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

Any other ideas?

Re: Set analysis to retrieve YtD total

Miguel_Angel_Baeyens — Sat, 15 Mar 2014 10:53:29 GMT

If the code is correct, then

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

Miguel

Re: Set analysis to retrieve YtD total

Sat, 15 Mar 2014 11:09:43 GMT

HI Max,

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

Best regards,

Dorine

Re: Set analysis to retrieve YtD total

Sat, 15 Mar 2014 12:04:09 GMT

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?

Re: Set analysis to retrieve YtD total

PrashantSangle — Sat, 15 Mar 2014 12:16:23 GMT

Hi,

don't nullify Year field.

Like this

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