Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikers!
I have 2 fields: OrderDate -> 01/01/2016 and Sales.
To calculate Current YTD im using this measure: =SUM({$<OrderDate = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}Sales) and it works well.
To calculate Previous YTD im using: SUM({$<OrderDate = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( MonthEnd (Today()),-12))'}>}Sales) and it works well too!
But now i want to calculate the same period of the previous year. Example:
Im calculating YTD: Sales from 01/01/2016 to 27/07/2016
I want to calculate from previous YTD: Sales from 01/01/2015 to 27/07/2015..
Anyone have any idea?
Thanks a lot!!
Hi Paula
I always find it easiest to use flags in the calendar script using yeartodate function
Example:
YearToDate(OrderDate,0 ,1,today()) as CurYTDFlag,
YearToDate(OrderDate,-1,1,today()) as LastYTDFlag,
It also makes the set analysis easier in the user interface
set analysis example:
sum({<$(CurYTDFlag)={'-1'}>} Sales)
Thanks
Dan
Thank you!
I will try with flags!
It looks some easy but...
How can i compare this Current YTD with THE SAME PERIOD from Previous YTD??
Regards,
Paula
maybe removing monthend
To calculate Previous YTD im using: SUM({$<OrderDate = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( MonthEnd (Today()),-12))'}>}Sales) and it works well too!
SUM({$<OrderDate = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( (Today()),-12))'}>}Sales)
if you wanted a growth figure then use something like the below:
num(sum({<$(CurYTDFlag)={'-1'}>} Sales)/sum({<$(LastYTDFlag)={'-1'}>} Sales)-1,'#,##0.00%')
Thanks for you reply Maxgro,
but it doesn't runs well.
Regards,Paula.
How about this:
Sum({$<OrderDate = {"$(='>=' & YearStart(AddMonths(Today(), -12)) & '<=' & AddMonths(Today(), -12))"}>}Sales)
If you put this -> ='>=' & YearStart(AddMonths(Today(), -12)) & '<=' & AddMonths(Today(), -12) in the text box, it should show you the range (01/01/2015 to 27/07/2015) in the format of your OrderDate field.
Hi Paula
Did any of the responses solve your query?
Please mark one as correct to help other community users.
Thanks
Dan