topic Re: SET EXPRESSION in QlikView

SET EXPRESSION

upaliwije — Fri, 02 Dec 2016 08:01:57 GMT

if((sum({<MO_NM={'MOTOR'}>}[PREMIUM])

/sum({<MO_NM={'MOTOR'}>}[R_BUDGET])*100>=100

AND

(sum({<MO_NM={'NON_MOTOR'}>}[PREMIUM])

/(sum({<MO_NM={'NON_MOTOR'}>}[R_BUDGET]))))*100>=100,

sum([PREMIUM]))

I have above expression which does not work. Pls help me to correct it

I want both Motor and Non Motor to be >= 100% and then sum the Premium

Re: SET EXPRESSION

tresB — Fri, 02 Dec 2016 08:24:58 GMT

May be few bracket corrections would help, like:

if((sum({<MO_NM={'MOTOR'}>}[PREMIUM])

/sum({<MO_NM={'MOTOR'}>}[R_BUDGET])*100)>=100

AND

(sum({<MO_NM={'NON_MOTOR'}>}[PREMIUM])

/sum({<MO_NM={'NON_MOTOR'}>}[R_BUDGET])*100)>=100,

sum([PREMIUM]))

Note : removed few brackets as well.

Re: SET EXPRESSION

upaliwije — Fri, 02 Dec 2016 08:31:42 GMT

Expression shows Ok message . But Results are not shown

Re: SET EXPRESSION

tresB — Fri, 02 Dec 2016 08:33:47 GMT

Could you post your representative sample qvw?

Re: SET EXPRESSION

upaliwije — Fri, 02 Dec 2016 09:33:18 GMT

I attach h/w my document. Pls help

Re: SET EXPRESSION

tresB — Fri, 02 Dec 2016 09:41:57 GMT

In data you have 'NOM_MOTOR' rather than 'NON_MOTOR'.

Re: SET EXPRESSION

rido1421 — Fri, 02 Dec 2016 09:53:00 GMT

Hi Upali

Actual:

if((sum({<MONM={'MOTOR'}>}[PREMIUM])

/sum({<MONM={'MOTOR'}>}[BUDGET])*100)>=100

AND

(sum({<MONM={'NOM_MOTOR'}>}[PREMIUM])

/sum({<MONM={'NOM_MOTOR'}>}[BUDGET])*100)>=100,

sum([PREMIUM]))

Budget:

if((sum({<MONM={'MOTOR'}>}[PREMIUM])

/sum({<MONM={'MOTOR'}>}[BUDGET])*100)>=100

AND

(sum({<MONM={'NOM_MOTOR'}>}[PREMIUM])

/sum({<MONM={'NOM_MOTOR'}>}[BUDGET])*100)>=100,

sum([BUDGET]))

You should also remove MONM as a Dimension as it will make the chart disappear when you expand it.

Maybe change to a straight table instead of Pivot?

Re: SET EXPRESSION

upaliwije — Fri, 02 Dec 2016 09:56:39 GMT

Thank for hilighting. Even if it is corrected the resul is not shown.

Re: SET EXPRESSION

rido1421 — Fri, 02 Dec 2016 10:00:42 GMT

Please have a look at this

Re: SET EXPRESSION

upaliwije — Fri, 02 Dec 2016 10:02:33 GMT

Thanks

I want to to MONM also to appear in the chart

Re: SET EXPRESSION

rido1421 — Fri, 02 Dec 2016 12:47:07 GMT

I believe it is working correctly based on the if statement you have applied,

if you look at the chart in the qvw, the 1st part and 2nd part of the expression are never both >=100 in the actual or budgets when looking at it with MNOM as a dimension