Help with set analysis - pivot table

[Anonymous]

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?

[Anonymous]

Hi

try it!

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

[#3]

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.

[Anonymous]

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:

[jonathandienst]

Hi

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

[Anonymous]

Hi

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

[Anonymous]

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

[#8]

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.