I have an issue with YTD and especially YTD-1

[OmarBenSalem]

Hi folks,

So as It's mentionned, I'm having some problems when dealing with the YTD-1,

my data is only between the years 2014 and 2015, so if I choose a date in 2014, the YTD-1 column has to show 0 as content, but it's not.

Here are my 2 expressions for YTD and YTD-1:

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [Chiffre d'affaires])

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(YearStart(Max(DateNum)-1)))<=$(=Max(DateNum)-1)"}>} [Chiffre d'affaires])

dateNum is created as so : Floor([Date Ventes]) AS DateNum,

What should I change?

[Oleg_Troyansky]

Hi Omar,

in your formula, your lower bound is calculated as the first day of the current year - 1 day (I think you were shooting for - 1 year, right?) So, here is a similar formula that seems to work correctly for me:

Sum({<Year=, Month=, Quarter=, Date= , NumDate={">=$(=Num(Addmonths(YearStart(Max(NumDate)), -12)))<=$(=num(YearStart(Max(NumDate))-1))"}>} Value)

You need to adjust the field names to yours...

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

[OmarBenSalem]

Hi Oleg and thanks for the reply, I appreciate it,

I've tried to do as you said but now the column of the YTD-1 only shows 0 as a result:

YTD:

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [Chiffre d'affaires])

YTD-1 :

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(Addmonths(YearStart(Max(NumDate)), -12)))<=$(=num(YearStart(Max(NumDate))-1))"}>} [Chiffre d'affaires])

I really am confused !

[Oleg_Troyansky]

Omar,

if you clear the labels on your expressions, you will be able to see the actual dates (numbers) that the expression is trying to use as the min and the max - perhaps you will be able to find the problem there.

cheers,

Oleg Troyansky

[OmarBenSalem · Accepted solution]

I have fixed the issue.

In fact, I had a problem with the date format, I've changed it as so :

date([Date Ventes],'DD/MM/YYYY') as [Date Ventes]

as expressions, here the 2 I've used:

YTD:

Sum({<Year=, Month=, Quarter=, [Date Ventes]= , DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [Chiffre d'affaires])

YTD-1

Sum({<Year=, Month=, Quarter=, [Date Ventes]=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=Num(addyears(max([Date Ventes]),-1)))"}>}[Chiffre d'affaires])

And all went good