Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rpsrathete
New Contributor

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

Tags (1)
1 Solution

Accepted Solutions
rangam_s
Contributor II

Re: YTD based on Selected Month

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.

25 Replies
mdmukramali
Valued Contributor II

Re: YTD based on Selected Month

Hi,

Qlik Sense App: Prior Period Comparison with Set Analsysis

Follow the above the link.

You will find example.

drillnaut
Valued Contributor

Re: YTD based on Selected Month

Master Calendar might help.  The best thing I can see an MC doing is that you could have a 'Current Year' flag.  And then you could do something to the effect of

PSEUDO Set Analsysis i.e It probably won't parse correctly.

SUM ({<[Current Year]=1, EffectiveDate="{=$(=< MAX(Effective Date)}">} Money)

There may be a cleaner and more clever way to get what you are looking for, but this is off the top of my head.

rpsrathete
New Contributor

Re: YTD based on Selected Month

It didn't work.

I used the expression below

SUM ({<Year_Policy_Eff_and_Acct_Eff_Merged={2018},Policy_Eff_and_Acct_Eff_Merged="{=$(=< MAX(Policy_Eff_and_Acct_Eff_Merged)}">} TotalRevenue_INV)

Year_Policy_Eff_and_Acct_Eff_Merged is a year field I created in the data load

Thanks

rpsrathete
New Contributor

Re: YTD based on Selected Month

This is how the date field created in the script

Timestamp(Effective_HR,'MM/DD/YYYY') as Policy_Eff_and_Acct_Eff_Merged,

arvind654
Honored Contributor II

Re: YTD based on Selected Month

Maybe just use

SUM ({< Month = {"<=$(=num(Month))"} >} TotalRevenue_INV)

drillnaut
Valued Contributor

Re: YTD based on Selected Month

That will give him every month LESS than the selected Month.  So if he selects March, he will get February 2018, January 2018, February 2017, January 2017 and so on.

arvind654
Honored Contributor II

Re: YTD based on Selected Month

I don;t think so...

I agree with you that it would give all the previous months from the selected months but it would only give for the selected year not the prior years.

I use this for YTD or am I wrong?

unless you use

Year={$(=(Max(Year))-1)}  in same expression then it gives you prior year YTD

rpsrathete
New Contributor

Re: YTD based on Selected Month

Hey Shahbaz,

I am still new to Qlik Sense, so below question might me stupid to ask,

But when it say "Month" should I be using the Month(Policy_Eff_and_Acct_Eff_Merged) in the above expression or it means something different?

arvind654
Honored Contributor II

Re: YTD based on Selected Month

Well I'm new too

But in my expression Month = Jan, Feb, Mar... Dec

So use your Month field which has those...

I guess it should work..