Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
rpsrathete
Contributor II
Contributor II

YTD based on Selected Month

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

25 Replies
rpsrathete
Contributor II
Contributor II
Author

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
Creator II
Creator II

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.

View solution in original post

rpsrathete
Contributor II
Contributor II
Author

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
Creator II
Creator II

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)

rpsrathete
Contributor II
Contributor II
Author

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

camron_allan
Contributor II
Contributor II

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.