topic Re: Set analysis MTD vs. YTD in QlikView

Set analysis MTD vs. YTD

Anonymous — Mon, 27 Oct 2014 20:15:01 GMT

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

Re: Set analysis MTD vs. YTD

JonnyPoole — Mon, 27 Oct 2014 20:25:21 GMT

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

Re: Set analysis MTD vs. YTD

Anonymous — Tue, 28 Oct 2014 13:11:44 GMT

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?

Re: Set analysis MTD vs. YTD

JonnyPoole — Tue, 28 Oct 2014 13:25:09 GMT

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

Re: Set analysis MTD vs. YTD

Anonymous — Tue, 28 Oct 2014 13:37:32 GMT

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?

Re: Set analysis MTD vs. YTD

Anonymous — Tue, 28 Oct 2014 13:49:57 GMT

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?

Re: Set analysis MTD vs. YTD

JonnyPoole — Tue, 28 Oct 2014 13:59:14 GMT

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))"}

Re: Set analysis MTD vs. YTD

JonnyPoole — Tue, 28 Oct 2014 14:00:31 GMT

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.

Re: Set analysis MTD vs. YTD

Anonymous — Tue, 28 Oct 2014 14:45:03 GMT

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!