Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis to retrieve YtD total

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

1 Solution

Accepted Solutions
PrashantSangle

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)

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

7 Replies
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

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)

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

Not applicable

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

Not applicable

HI Max,

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

Best regards,

Dorine

Not applicable

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)

Great dreamer's dreams never fulfilled, they are always transcended.