topic Re: YTD based on Selected Month in Visualization and Usability

YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 15:07:12 GMT

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

Re: YTD based on Selected Month

mdmukramali — Thu, 21 Jun 2018 15:28:45 GMT

Hi,

Qlik Sense App: Prior Period Comparison with Set Analsysis

Follow the above the link.

You will find example.

Re: YTD based on Selected Month

JustinDallas — Thu, 21 Jun 2018 15:30:22 GMT

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.

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 16:00:24 GMT

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

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 16:01:40 GMT

This is how the date field created in the script

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

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 17:25:47 GMT

Maybe just use

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

Re: YTD based on Selected Month

JustinDallas — Thu, 21 Jun 2018 17:49:41 GMT

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.

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 17:54:55 GMT

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

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 18:01:17 GMT

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?

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 18:04:56 GMT

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

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 18:12:25 GMT

I tried the above expression and all values were 0.

Expression used:

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

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 18:16:58 GMT

It should work...

Do you have a sample for your app?

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 18:20:23 GMT

Or try below one

SUM ({< Year=, Month=,YOURDATEFIELDHERE= {'>=$(=YearStart(Max(YOURDATEFIELDHERE)))<=$(=Max(YOURDATEFIELDHERE))'} >} TotalRevenue_INV)

Use your Date field in the bold text...

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 18:31:09 GMT

Didn't work.

Is there something I am doing wrong?

SUM ({< Year=, Month=,Policy_Eff_and_Acct_Eff_Merged= {'>=$(=YearStart(Max(Policy_Eff_and_Acct_Eff_Merged)))<=$(=Max(Policy_Eff_and_Acct_Eff_Merged))'} >} TotalRevenue_INV)

Date fields in data load editior:

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

Year(Effective_HR) as Year_Policy_Eff_and_Acct_Eff_Merged,

Month(Effective_HR) as Month_Policy_Eff_and_Acct_Eff_Merged,

Screenshot of Result:

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 18:43:44 GMT

First...

What are the names of your Year , Month & Quarter Fields?

You used Month_Policy_Eff_and_Acct_Eff_Merged as your Month in previous expression

and now you used Month=,Policy_Eff_and_Acct_Eff_Merged=

Do you need a timestamp in your date field?

Maybe just use

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

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_INV)

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

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 18:58:33 GMT

Year field Name: Year_Policy_Eff_and_Acct_Eff_Merged

Month field name: Month_Policy_Eff_and_Acct_Eff_Merged

I don't have Quarter field.

I need timestamp for some other analysis.

I have tried both the above equations and it didn't work.

I really appreciate your help Shahbaz.

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 19:00:17 GMT

Create a new Date field without stamp field

And use use that field in SUM ({< Year=, Month=,YOURDATEFIELDHERE= {'>=$(=YearStart(Max(YOURDATEFIELDHERE)))<=$(=Max(YOURDATEFIELDHERE))'} >} TotalRevenue_INV)

and it's ok np, a sample app would help

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 19:08:01 GMT

It is sensitive data therefore I won't be able to share sample.

I am currently in process to create Policy_Eff_and_Acct_Eff_Merged field without Timestamp and using Date field instead.

Once I have that ready, should I try this equation again as Month and Year field don't need any changes

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_INV)

Re: YTD based on Selected Month

MK9885 — Thu, 21 Jun 2018 19:16:39 GMT

Yes you can try, along with your newly created date field

SUM ({< Year_Policy_Eff_and_Acct_Eff_Merged=,

Month_Policy_Eff_and_Acct_Eff_Merged=,

Policy_Eff_and_Acct_Eff_Merged_Without Timestamp= {'>=$(=YearStart(Max(Policy_Eff_and_Acct_Eff_Merged_Without Timestamp)))<=$(=Max(Policy_Eff_and_Acct_Eff_Merged_Without Timestamp ))'} >} TotalRevenue_INV)

Re: YTD based on Selected Month

Anonymous — Thu, 21 Jun 2018 20:16:45 GMT

Hello Shahbaz,

I tried with new date field but still I am getting zero.

Is there any alternate method to get this done?

Thanks