topic Re: Creating flags for InMTD and InQTD in App Development

Creating flags for InMTD and InQTD

y_grynechko — Thu, 10 Jan 2019 08:08:58 GMT

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!

Re: Creating flags for InMTD and InQTD

sunny_talwar — Thu, 10 Jan 2019 12:18:27 GMT

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

Re: Creating flags for InMTD and InQTD

y_grynechko — Fri, 11 Jan 2019 07:39:33 GMT

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.

Re: Creating flags for InMTD and InQTD

sunny_talwar — Fri, 11 Jan 2019 12:07:13 GMT

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?

Re: Creating flags for InMTD and InQTD

y_grynechko — Fri, 11 Jan 2019 13:20:54 GMT

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:

Re: Creating flags for InMTD and InQTD

sunny_talwar — Fri, 11 Jan 2019 13:25:19 GMT

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'}>}

Re: Creating flags for InMTD and InQTD

y_grynechko — Fri, 11 Jan 2019 13:44:10 GMT

It works, thank you Sunny!