Set analysis MTD vs. YTD

[Anonymous]

Hello,

I am looking for some help on a graph I am trying to produce.  I have tried two ways to create the graph and I am missing a piece of the syntax.

Here is what I am trying to do:

Show the top 5 variations of this year vs. last in Net Premium.  My data is in MTD format, but this year's data is only through August.  How do I exclude the last 4 months, while also making the graph dynamic based on user selections.  Here is the syntax I have tried so far:

=(

SUM({$<YEAR={2014}, [MONTH] = {"<=8"}>} [NET WRIT PREM])-SUM({$<YEAR={2013}, [MONTH] = {"<=8"}>}[NET WRIT PREM])) / SUM({$<YEAR={2013}, [MONTH] = {"<=8"}>}[NET WRIT PREM])

The above syntax is not dynamic based on month selections, and will not change.  It does seem to exclude any month after August.

Syntax 2:

The first curly bracket after Month is bringing in a syntax error.

=(SUM({$<YEAR={2014}, [MONTH] -= {"9","10","11","12"}>} [NET WRIT PREM])

-SUM({$<YEAR={2013}, [MONTH] -= {"9","10","11","12"}>}[NET WRIT PREM])) /

SUM({$<YEAR={2013}, [MONTH] -= {"9","10","11","12"}>}[NET WRIT PREM])

Looking at the references and beginners guides out there I believe I have close to the correct syntax.

Thank you for the help,

Justin

[JonnyPoole]

I think by having a SET MODIFIER on Month, you are not going to have user selections on MONTH be 'listened too'.

The following will be dynamic with Months but not Year.  You can introduce a variable to store the max(Year) based on user selections and use that in the filter.

=

(

     SUM(  if( Year=2014 and Month <= 8,  [NET WRIT PREM] ))

     -

     SUM(   if( Year=2014 and Month <= 8, [NET WRIT PREM]))

     ) 

/

SUM( if( Year=2014 and Month <= 8 , [NET WRIT PREM]))

[Anonymous]

Jonathan,

Thank you again for the help.  This file seems to calculate incorrectly.  I am looking for the variance from 2014 and 2013.  I changed the last two years to 2013 to get a variance.  When I quality checked against the formula that was only dynamic if the user had a month before August selected, they did not return the same value.  I will include the picture.  Also, I am intrigued by the variable for max(Year).  Could you speak to that as well?

[JonnyPoole]

i looked at my expression and realize i left Year=2014 everywhere.  Below i updated the denominator as well as the 2nd sum in the numerator to be 2013.  But i wasn't clear on your response... did you already try this and its incorrect ?  I'd like to play with the QVW if possible.  If you create a variable with a formula of max(Year), the variable will update with every user selection to calculate the maximum year. You can use the variable to good effect in SET ANALYSIS etc...

=

(

     SUM(  if( Year=2014 and Month <= 8,  [NET WRIT PREM] ))

     -

     SUM(   if( Year=2013 and Month <= 8, [NET WRIT PREM]))

     )

/

SUM( if( Year=2013 and Month <= 8 , [NET WRIT PREM]))

[Anonymous]

Jonathan,

I tried that formula and it did not return values correctly. Thank you again for the help. I tried this formula and it seems to work:

=

IF(MONTH<='8', (SUM({$<YEAR={2014}>} [NET WRIT PREM])-SUM({$<YEAR={2013}>}[NET WRIT PREM])) / SUM({$<YEAR={2013}>}[NET WRIT PREM]),
(SUM({$<YEAR={2014}, MONTH={'1','2','3','4','5','6','7','8'}>} [NET WRIT PREM])-SUM({$<YEAR={2013}, MONTH={'1','2','3','4','5','6','7','8'}>}[NET WRIT PREM])) / SUM({$<YEAR={2013}, MONTH={'1','2','3','4','5','6','7','8'}>}[NET WRIT PREM]))

It seems very inefficient and will need to be updated monthly as I introduce a new set of data.  Any thoughts?

I have created a formula call vMaxYear.  How could I introduce this into this example?

[Anonymous]

One other quick question on this.  When I have updated to the formula I included, I am trying to show the top / bottom 5 states.  The top 5 seems to be working correctly, but the bottom five is behaving oddly.  When there are no selections, it will only show 4 states.  When there is a selection for month, it will show 5 states properly.  Any way to fix this or thoughts on what is causing this?

[JonnyPoole · Accepted solution]

So for the max(Year) thing, the following syntax should work in your SET ANALYSIS. So instead of 2014 , if the user selected 2013 only, the max year would be 2013.

YEAR={$(=Max(Year))}

if your variable had a definition of:   =Max(Year)   

...then you could leverage the variable in the SET ANALYSIS like this

Year={$(vMaxYear)}

Take care with your variable definition. If you had this:   Max(Year)   

...which has no equal sign in front it can still work but you'd need to update the SET ANALYSIS to

Year={"=$(vMaxYear)"}

You can do something similar for Month. Use the double quotes because its a 'search' expression. 

MONTH={"<$(=Max(Month))"}

[JonnyPoole]

I'm not sure , are you showing 'OTHERS' or hiding 'OTHERS' ?  'OTHERS'  counts for 1 of the buckets. Do you think there is a NULL value ?  Try playing with the presentation tab suppress zeros property or the dimensions tab 'suppress nulls' property.

[Anonymous]

Jonathan,

It all worked great!

When I selected the suppress nulls property that showed the fifth element, even though that number has a value.  That seemed weird to me.  Thanks again!