App Development

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for
Did you mean:
Creator III

Creating flags for InMTD and InQTD

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!

Labels (2)

• Set Analysis

1 Solution

Accepted Solutions
MVP

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'}>}`
6 Replies
MVP

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```
Creator III
Author

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.

MVP

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?

Creator III
Author

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:

MVP

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'}>}`
Creator III
Author

It works, thank you Sunny!

Tags
Community Browser