YTD based on Selected Month

[Anonymous]

Hello,

I am trying to create YTD sum of revenue using a Date field (EffectiveDate) in the data set.

I tried many expressions posted in community but wasn't able to generate YTD based on month selection.

What I am looking for:

If Feb is selected then sum of revenue for Jan + Feb with a condition of BType = 'New Business'

Do I need to generate master calendar to get this done or it can be done based on a date field?

Any help would be really appreciated.

Thank you

[Anonymous]

Hello Shahbaz,

1)

SUM ({< Year_Policy_Eff_and_Acct_Eff_Merged=,Month_Policy_Eff_and_Acct_Eff_Merged=,Policy_Eff_and_Acct_Eff_Merged= {">=$(=YearStart(Max(Policy_Eff_and_Acct_Eff_Merged)))<=$(=Max(Policy_Eff_and_Acct_Eff_Merged ))"} >} TotalRevenue_HR)

2)

sum({$ < Year_Policy_Eff_and_Acct_Eff_Merged ={2018} , Month_Policy_Eff_and_Acct_Eff_Merged= , Policy_Eff_and_Acct_Eff_Merged = {">={1}  <=$(=max(Policy_Eff_and_Acct_Eff_Merged))"}>} TotalRevenue_HR)

Above both expressions works.

But the first one will show '0' until I select a  year and how to generate similar expression for previous year?

I want to compare YTD based on select month with Previous years numbers

Thanks

[rangam_s · Accepted solution]

Try this

It will generate current year (Selected Year in the filter else maximum year in the data) YTD Sales

sum({<Year_Policy_Eff_and_Acct_Eff_Merged={"$(=Max(Year_Policy_Eff_and_Acct_Eff_Merged))"},EffectiveDate={"<=$(=Max(EffectiveDate))"},Month=>}TotalRevenue_INV)

Previous Year YTD Sales (Till Same month and Date):

sum({<Year_Policy_Eff_and_Acct_Eff_Merged={"$(=Max(Year_Policy_Eff_and_Acct_Eff_Merged)-1)"},EffectiveDate={"<=$(=AddYears(Max(EffectiveDate),-1))"},Month=>}TotalRevenue_INV)

Note: In case, if you have any date filters which are affecting the data then nullify those filters as well in the set expression.

[Anonymous]

Thanks Rangam.

In the above expressions, the year selected in the filter pane will be considered as current year?

What will be the expression for current year MTD and Previous year MTD based on select month?

What I am looking for is:

Current year MTD, Previous Year MTD, YTD and Previous YTD.

example: if Feb is selected then revenue for Feb this year, previous year, YTD revenue, Previous YTD revenue

Thanks for your help

[rangam_s]

Yes, Selected Year will be considered as current year.

Try this.

Current Month MTD:

sum({<EffectiveDate={"<=$(=Max(EffectiveDate))","<=$(=MonthStart(Max(EffectiveDate)))"},Month=>}TotalRevenue_INV)

Previous Year MTD Sales:

sum({<EffectiveDate={"<=$(=AddYears(MonthStart(Max(EffectiveDate)),-1))"},Month=,Year_Policy_Eff_and_Acct_Eff_Merged=>}TotalRevenue_INV)

[Anonymous]

Hello Rangam,

If Year is not selected in the filter pan , how can I default it to current Year instead of Max year in the expressions?

Thanks

[Anonymous]

These types of time-related set expressions can get so messy.

You should try QlikRTP!  It's free.

You can read about it in my post, here.

RTP stands for Relative Time Periods.  This script leverages your existing month dimension to add the following relative time elements to your app:

• To-Date ranges such as YTD and QTD
• Relative time periods such as "Previous Year", "Previous Month", or “same period last year”
• Rolling periods which are used for moving averages. (Ex. "Rolling 3 month average")
• Year-over-Year and MoM growth metrics

Not only that, these RTPs will work for any anchor month! (not just the current period)

This approach greatly simplifies your measures by only requiring two set modifiers.