Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
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!

1 Solution

Accepted Solutions
Highlighted

Re: Creating flags for InMTD and InQTD

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

View solution in original post

6 Replies
Highlighted

Re: Creating flags for InMTD and InQTD

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
Highlighted
Creator III
Creator III

Re: Creating flags for InMTD and InQTD

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. 

Highlighted

Re: Creating flags for InMTD and InQTD

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?

Highlighted
Creator III
Creator III

Re: Creating flags for InMTD and InQTD

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:

data.JPG

Highlighted

Re: Creating flags for InMTD and InQTD

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

View solution in original post

Highlighted
Creator III
Creator III

Re: Creating flags for InMTD and InQTD

It works, thank you Sunny!