topic Re: Set Expression Calculation in QlikView

Set Expression Calculation

upaliwije — Fri, 07 Dec 2018 06:24:58 GMT

Hi Friends

I have Pivot table with some calculation. But It returns 0 value. Pls have a look at below image and advise me where I am wrong. My below expression show zero value

sum({<MonthYear={'FMonthYear'}>}PREMIUM)

Re: Set Expression Calculation

shiveshsingh — Fri, 07 Dec 2018 06:28:09 GMT

May be this?

sum({<MonthYear={"=$(FMonthYear)"}>}PREMIUM)

Re: Set Expression Calculation

upaliwije — Fri, 07 Dec 2018 06:53:45 GMT

Thanks Friend

Still the result shown is zero

Re: Set Expression Calculation

tresB — Fri, 07 Dec 2018 06:56:56 GMT

What is FMonthYear, is it a field or a variable?

Re: Set Expression Calculation

upaliwije — Fri, 07 Dec 2018 07:19:20 GMT

it is a field created with following syntax

Num(Year(POL_PERIOD_FROM)) & Num(Month(POL_PERIOD_FROM), '00')AS FMonthYear;

Re: Set Expression Calculation

tresB — Fri, 07 Dec 2018 07:32:22 GMT

And what are trying to get by the expression: sum({<MonthYear={'FMonthYear'}>}PREMIUM) ? Could you explain words? Is it like - 'sum premium where MonthYear and FMonthYear are equal' ?

Re: Set Expression Calculation

upaliwije — Fri, 07 Dec 2018 07:38:27 GMT

I want to sum the premium when MonthYear and FMonthYear are equal. If you look at my image you see similar result when Month=Fmonth

Re: Set Expression Calculation

tresB — Fri, 07 Dec 2018 07:42:43 GMT

I think the approach you are taking might not be very convincing. Could you are your app sample and explain the expected output against that?

Re: Set Expression Calculation

upaliwije — Fri, 07 Dec 2018 08:23:53 GMT

Thanks Tresesco,

I have some insurance policy date in my attached QV document. In this data there are policies and subsequent cancellation as well. I want to show the Premium amount in a particular month and below that the subsequent cancellation relating to such policies under the same column. Eg : POLICY_NO AH00141A0000032 IS Introduced IN Dec 2015 and cancelled in Mar 2016. So I want to show both transactions in the same column against different months (FvMonthYear). I have shown below the images for your easy reference please. Kindly help me to develop this pivot table. Thanks

Re: Set Expression Calculation

tresB — Fri, 07 Dec 2018 08:46:35 GMT

I am not yet quite sure about your requirement. Try:

sum({<MonthYear={"=MonthYear=FMonthYear"}>}PREMIUM)

Re: Set Expression Calculation

upaliwije — Fri, 07 Dec 2018 08:46:23 GMT

Pls help me with my real requirement