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

[mdmukramali]

Hi,

Qlik Sense App: Prior Period Comparison with Set Analsysis

Follow the above the link.

You will find example.

[JustinDallas]

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.

[Anonymous]

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

[Anonymous]

This is how the date field created in the script

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

[MK9885]

Maybe just use

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

[JustinDallas]

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.

[MK9885]

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

[Anonymous]

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?

[MK9885]

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..