Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
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

25 Replies
mdmukramali
Specialist III
Specialist III

Hi,

Qlik Sense App: Prior Period Comparison with Set Analsysis

Follow the above the link.

You will find example.

JustinDallas
Specialist III
Specialist III

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
Not applicable
Author

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
Not applicable
Author

This is how the date field created in the script

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

MK9885
Master II
Master II

Maybe just use

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

JustinDallas
Specialist III
Specialist III

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

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
Not applicable
Author

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

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