topic Help with set analysis - pivot table in QlikView

Help with set analysis - pivot table

Anonymous — Fri, 24 Oct 2014 20:21:32 GMT

Hello,

I am looking for only the year 2014 which I believe to be correct. Here is how the table shows:

I am having some difficulty and would like to get an opinion on how to best fix this statement. I am trying to create a pivot table that will show premium by line of business. When I show premium by month it works fine, but when I am trying to switch to line of business it gets rid of the top line of business. Here is the syntax I am trying to use:

=SUM({<YEAR ={2014}>}[SumOfNET WRIT PREM])-Above(SUM({<YEAR ={2014}>}[SumOfNET WRIT PREM]))

I am looking for only the year 2014 which I believe to be correct. Here is how the table shows:

I have tried to switch to below, which also seems to be incorrect. Any thoughts on how to best correct this problem?

Re: Help with set analysis - pivot table

Anonymous — Fri, 24 Oct 2014 22:49:39 GMT

try it!

=SUM({$<YEAR ={2014}>}[SumOfNET WRIT PREM])-Above(SUM({$<YEAR={2014}>}[SumOfNET WRIT PREM]))

Re: Help with set analysis - pivot table

Sat, 25 Oct 2014 04:48:38 GMT

Hi,

It maybe since for the first record ABOVE returns a NULL hence the subtraction evaluates to null.\. Hence use isnull to detect null and replace it by zero.

=SUM({<YEAR ={2014}>}[SumOfNET WRIT PREM])-if(isnull(Above(SUM({<YEAR ={2014}>}[SumOfNET WRIT PREM]))),0,Above(SUM({<YEAR ={2014}>}[SumOfNET WRIT PREM])))

Thanks,

Shoubhik.

Re: Help with set analysis - pivot table

Anonymous — Mon, 27 Oct 2014 14:02:41 GMT

Jannet and Shoubhik,

Thank you for the help. The second statement with the if(isnull has taken a step forward. Just a few issues that I am not sure how to fix:

1. When no selections are made, only Auto returns the correct value. When selections are made, the correct value is then shown.

2. My data is in the form YTD, and when I try to select a month and a year, ie Feb 2014, the function no longer seems to work in MTD and returns the total for the year. In my example, Jan is 177 and Feb is 178. In the table when selecting Feb it returns 355. Is there a fix when the user selects a month, or is the only fix to get MTD data?

Here is my picture to help illustrate:

Re: Help with set analysis - pivot table

jonathandienst — Mon, 27 Oct 2014 14:24:15 GMT

I would use Alt() instead of If(IsNull()) as it is simpler and probably more efficient.

=SUM({<YEAR ={2014}>} [SumOfNET WRIT PREM])-Alt(Above(SUM({<YEAR ={2014}>} [SumOfNET WRIT PREM])),0)

HTH

Jonathan

Re: Help with set analysis - pivot table

Anonymous — Mon, 27 Oct 2014 15:25:50 GMT

you must declare two variables in your Field_date than return the max and min .

min(field_date)

max(field_date)

you must replace variables on your expression

SUM({<Field_date={"<=$(Name_variable_Max)>=$(Name_variable_min)"}>......

I hope I explained

Regards

Re: Help with set analysis - pivot table

Anonymous — Mon, 27 Oct 2014 16:06:16 GMT

Jannet,

Not entirely sure as I am new how to properly set this up.

In setting the variables what values should I set for them?

Also, could you show me the full syntax so I can obtain the correct results. Ultimately I am using Year, Month, and then Net Writ Prem, and Line of Business.

Thank you for all the help so far,

Justin

Re: Re: Help with set analysis - pivot table

Wed, 29 Oct 2014 02:35:34 GMT

Hi Justin,

Is there any way you can upload your sample qvw or sample record set. I tried using a sample dataset for the condition you specified but getting correct results though. !

Thanks,

Shoubhik.