
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set analysis MTD vs. YTD
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
Accepted Solutions

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))"}

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))"}

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
