Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to figure out how to get data for MTD and QTD dynamically. I have this flags created:
If(DayNumberOfYear(D) <= DayNumberOfYear($(v_Today)), 1, 0 ) as IsInYTD,
If(DayNumberOfQuarter(D) <= DayNumberOfQuarter($(v_Today)), 1, 0) as IsInQTD,
If(Day(D) <= Day($(v_Today)), 1, 0) as IsInMTD
YTD works fine, when I am doing comparison of the year to date sales to last year to date sales:
Sum( { $<[IsInYTD]={1}, Year ={"$(=Max(Year))"},Month=> } Sales ) - Sum( { $<[IsInYTD]={1}, Year ={"$(=Max(Year)-1)"},Month=> } Sales )
But the same logic doest work for MTD and QTD. It just gives me whole year's sales.
Sum( { <[IsInMTD]= {1}, Year ={"$(=Max(Year))"}> } [Voucher USD Amount] ).
What I needs is to be able to compare data from same periods. For example:
Today is 1/10/2019
When I select year 2018 I want to compare data from 1/1/2018 to 1/10/2018 with data from 1/1/2017 to 1/10/2017
Thanks!
Then in that case, try this
If(DayNumberOfQuarter(D) <= DayNumberOfQuarter(Today()) and Ceil(Month(D)/3) = Ceil(Month(Today())/3), 1, 0) as IsInQTD, If(Day(D) <= Day(Today()) and Month(D) = Month(Today()), 1, 0) as IsInMTD
But now, make sure to use set analysis on Year field as well
For Max year
{<Year = {"$(=Max(Year))"}, IsInMTD = {'1'}>}
For Max year minus 1
{<Year = {"$(=Max(Year)-1)"}, IsInMTD = {'1'}>}
Try this
If(DayNumberOfQuarter(D) <= DayNumberOfQuarter($(v_Today)) and QuarterStart(D) = QuarterStart($(v_Today)), 1, 0) as IsInQTD, If(Day(D) <= Day($(v_Today)) and MonthStart(D) = MonthStart($(v_Today)), 1, 0) as IsInMTD
Sunny,
thank you for the reply but it works only for the current year. When I select any other year the values go to 0.
Are you looking to flag each year based on today's month, or quarter?
All of these will be flagged with 1
01/01/2016-01/11/2016
01/01/2017-01/11/2017
01/01/2018-01/11/2018
But this isn't based on any other selection, right? So, let's say you select 1st Nov, would you want the quarter flag to change to
09/01/2016-11/01/2016
09/01/2017-11/01/2017
09/01/2018-11/01/2018
This is not what you want, right?
Today is 1/11/2019 So after opening the app I will have 2 KPIs. One to show QTD sum of sales: from 1/1/2019 to 1/11/2019
and the other KPI to show sum of sales 1/1/2018 to 1/11/2018.
And this is easy to do but what is need is for it to change when user select different year. For example if user select's year 2017 KPI one will show sum of sales: from 1/1/2017 to 1/11/2017
and the other KPI to show sum of sales 1/1/2016 to 1/11/2016.
Data looks like this:
Then in that case, try this
If(DayNumberOfQuarter(D) <= DayNumberOfQuarter(Today()) and Ceil(Month(D)/3) = Ceil(Month(Today())/3), 1, 0) as IsInQTD, If(Day(D) <= Day(Today()) and Month(D) = Month(Today()), 1, 0) as IsInMTD
But now, make sure to use set analysis on Year field as well
For Max year
{<Year = {"$(=Max(Year))"}, IsInMTD = {'1'}>}
For Max year minus 1
{<Year = {"$(=Max(Year)-1)"}, IsInMTD = {'1'}>}
It works, thank you Sunny!