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
I am also running into this issue.
I want a KPI to just show current year's YTD.
IF someone selects a date on the dashboard then I'd like to show YTD for selected date, but for default just Current YTD.
Or even a KPI that ALWAYS shows current years' YTD and I could just call it "Current YTD Sales", etc...
Hi Josh,
If you need Current Years YTD then make a change in set analysis instead of Max(DateNum) use Today()
try the below one
Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today())))<=$(=Today())"}>} Sales)
Nice Article, I had edited from Month to Date to Week to Date for WTD(Typo Mistake)
Scotly, those coming from Master Calendar
MC:
Month(DateField) as Month,
Year(DateField) as Year,
Celi(Month(DateField)/3) as Quarter,
Week(DateField) as Week,
DateField is simple Date field. That is the reason he is using those. So, finally i want to say one thing which is You have one date field and then you want to split those into Fiscal year calendar
Good information. Thank you.
Hi, I tried to create the metrics with above method but getting the same result in all the metrics.
any idea what is wrong?
thanks
Viresh
Below are my expressions.
YTD -
sum({<Year=,Month=, Quarter=, [Order Date]=,DateNum={">=$(=Num(QuarterStart(max(DateNum)))))
<=$(=max(DateNum))"}>}Sales)
QTD -
sum({<Year=,Month=, Quarter=, [Order Date]=,DateNum={">=$(=Num(YearStart(max(DateNum)))))
<=$(=max(DateNum))"}>}Sales)
MTD -
sum({<Year=,Month=, Quarter=, [Order Date]=,DateNum={">=$(=Num(monthstartStart(max(DateNum)))))
<=$(=max(DateNum))"}>}Sales)
helpful, thanks.
Excelent
Good Work. Thank's for sharing
Saludos,
Enrique Colomer
Hi,
thank you for your post.
I did use the YTD function but it doesn't work for me.
I have a simple table with :
date | Amount |
---|---|
2015/12/31 | 43315257 |
2020/06/30 | 10087819 |
2020/07/31 | -1269887020 |
2020/08/30 | 7597395 |
i did use this expression :
sum({<YEAR={$(=($(vCurrentYear)))}, DATE={"<=$(=Max(DATE))"}> }Amount)
to calculate the amount of the current month till the amount of january of the current year but it doesn't work
I also tried this expression:
Sum({< DATE={">=$(=Num(YearStart(Max(DATE))))<=$(=(Max(DATE)))"}>} Amount)
But same thing it just calculate the amount of the current month and i don't know how to fix that :s
Any help ?
Thank you very much !
May be Remove Num in your second statement of first part