Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Im new to Qlikview and I need help with some expressions.
I would like to create a straight table with the expressions that shows TY Sale, TY Volume, LY Sales, TY sales and also TYTD volume/sales and LYTD volume /sales.
I use this for period ( month 09-18):
TY Volume: sum( {$< $(vPeriod_1) >} [WEIGHT NET] )
LY Volume: sum( {$< $(vPeriod_2) >} [WEIGHT NET] )
TY Sales: sum( {$< $(vPeriod_1) >} ([TURNOVER NET_$(vCurr)]) )
LY Sales: sum( {$< $(vPeriod_2) >} ([TURNOVER NET_$(vCurr)]) )
Now to TYTD (01-18 to 09-18):
TYTD: Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [WEIGHT NET])
And this is the one not working: I want to have the same month as above -1 year
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum) -1)))<=$(=Num(AddMonths(Max(DateNum) -12)))"}>} [WEIGHT NET])
But im only getting the same value as TYTD
What am i doing wrong?
Can this be done? Can i create a straight table with both columes for 1 month and in the same table show Year to date and previous Year to date?
Regards
Hi Doran. A little correction, just added 2 commas
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=Num(AddMonths(Max(DateNum), -12)))"}>} [WEIGHT NET])
Hi Andrey!
Thanks for the information.
I changed the expression but i still get the same value for
TYTD:
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} [WEIGHT NET])
And previous YTD:
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=Num(AddMonths(Max(DateNum), -12)))"}>} [WEIGHT NET])
I get the right number for TYTD but cant get the - 1 year for TYTD to work. they show the same value.
Regards
Hi, Dorian. You know, I tested the expression and it works fine for me. Can you share a sample?
Hi Andrey,
I dont know how to do that without exposing company data.
Do you have a tutorial or direction where to do this?
Regards
It seems i know what's the matter
1. Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum)))) <=$(=Max(DateNum))"}>} [WEIGHT NET])
2. Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=Num(AddMonths(Max(DateNum), -12)))"}>} [WEIGHT NET])
Just replace this YearStart with the MonthStart