Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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!
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 !
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
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