Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here
First to do this your data model should have the DateField in number format by applying floor
Similar to this
Floor(DateField) AS DateNum //it will gives you one whole number to represent date
YTD - Year To Date
A date should be selected and it will look for the Starting date of the year to the selected date.
Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014
Expression would be
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
QTD- Quarter to Date
In the place of year use Quarter
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
MTD- Month to Date
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
WTD- Week to Date
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}>} Sales)
Will keep your expression simple
Regards,
Celambarasan
Hi Celambarasan
This is an excellent post. Really helped me.
1 minor correction in the above expression.
Instead of AddYear it has to be AddYears
Thanks
Satish
Thanks Celambarasan.
Topics like these are generally confusing and you made it very clear.
Cheers,
Naresh
Hi Celambarasan,
Able to calculate the YTD from your expression but the MTD is not working. Using the following expression:
=Sum({<Month={$(=max(Month))}, Date={'>=$(=Num(MonthStart(Max(Date))))<=$(=Max(Date))'}>} [Sales])
Is your date field is in number format if not try the below
=Sum({<Month={$(=max(Month))}, Date={'>=$(=MonthStart(Max(Date)))<=$(=Date(Max(Date)))'}>} [Sales])
Hi Celambarasan,
thanks for the help..
Also i am trying to find the previous year YTD sales, using the following expressions to calculate:
1. =Sum({$<Year={$(=max(Year-1))}, Date={'>=$(=YearStart(Max(Date-365))))<=$(=Date(Max(Date-365)))'}>}[Sales]). This one is working.
2. =Sum({$<Year={$(=max(Year-1))}, Date={'>=$(=YearStart(MakeDate(max(Year-1),Month, Day(Date))))))<=$(=MakeDate(max(Year-1),Month,Day(Date))))'}>}[Sales]). This one is not wokring.
Can you please help on the second expression what may be the error in it.
i resolved it in a nicer way, i wanted to add a dimension called period so i can select or deselect it, that way i can use it in bookmarks, you can see my implementation, i wanted:
7 last days - 1w.
last month - 1m - 30 days.
last year - 365 days back.
you can see my solution in the attached file, you can add more periods or change the calender the same way you can see my comment with attachment in
Qlikview Financial periods (MTD, YTD, LMTD, LYYTD)
enjoy!
Hi Celambarasan,
By applying your Login am getting the calculation error.
QTD
=Sum({<Quarter={'$(vQuarter)'}, Quarter={'$(vQuarter)'}, Month={'$(vMaxMonth)'}, Week={'$(vWeek)'}, Date={'$(vMaxDate)'}, DateNum={'>=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))'}>} [Net Bookings]).
Its is showing me the same calculation as of Monthly basis.
Regards,
KK
Hi, Im new to Qlik, coming from Microstrategy , where I have to implement the YTD function ? I dont know where I have to put the function:
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)
I assume that "sales" is referencing the sales table (in my case it has another name)
thanks!
hi
you need to use the above expression while creating chart
see if the below helps
after reloading the data in Qlikview , right click on the ui Layer. Create chart and select chart type , click next and specify your dimension and click next , you will get an edit expression window. We need to enter the expression here.
hope it helps...
thanks
satish
Ok, thanks a lot for your response, it works, Im starting to understand the way qlikview works, I dont know if I can achieve the approach that I would like , I will try to explain: for example I want to create the same expression, but instead for one graphic for the entire document, so I would reuse it across other objects with other attributes, like YTD sales by month, YTD sales by city, etc.
Dont know if that is possible, is like declaring a metric like in microstrategy, or in Microsoft PowerBI (DAX)
thanks!!