topic Re: Condition in Expression in QlikView

Condition in Expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Newbie here... any suggestions on how to remove the Backlog Re-compete line (in red box) from the pivot below? I may just be using the wrong syntax but here is the main logic for the expression for FY17 Total:.

if (Layer -= 'Backlog' and Sub_Layer -= 'Re-compete', Sum({$<DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}>}DRVD_PRD_MTHLY_SPND_AMT_FY2ALL) )

TIA for your help!

Carol

Re: Condition in Expression

swuehl — Wed, 29 Jun 2016 20:34:47 GMT

Have a look at

Excluding values in Set Analysis

Identify an appropriate key field you can use within the set analysis, may something like TransactionID, then

Sum({$<TransactionID = e({<(Layer = {'Backlog'}, Sub_Layer = ['Re-compete'}>}), DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}>}DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

Re: Condition in Expression

sunny_talwar — Wed, 29 Jun 2016 20:36:21 GMT

May be this:

Sum({$<DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}, Layer -= {'Backlog', 'Re-compete'}>} DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

Re: Condition in Expression

vikramv — Wed, 29 Jun 2016 20:40:15 GMT

Corrected the spelling mistake ''Re-complete'' to "'Re-compete'"

Replace the dimension [Sub Layer]

with

if (Layer = 'Backlog' and [Sub Layer]='Re-compete',null(),[Sub Layer] ) and use supress null on this dimension checked

Keep the expression like...

Sum({$<DRVD_PRD_SUB_ROLLUP-={"NULL"},DRVD_PRD_STATUS-={"Lead"}>}DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

Re: Condition in Expression

Wed, 29 Jun 2016 21:52:31 GMT

Great idea, I thought it would work but it. I also tried simplifying the expression to like below and it also did not work:

Sum(DRVD_PRD_MTHLY_SPND_AMT_FY2ALL)

Re: Condition in Expression

Wed, 29 Jun 2016 21:55:02 GMT

Thanks Sunny, but it did not work.

Re: Condition in Expression

vikramv — Wed, 29 Jun 2016 23:17:57 GMT

Check if my correcting the spelling mistake ''Re-complete'' to 'Re-compete' ?

Re: Condition in Expression

trdandamudi — Wed, 29 Jun 2016 23:49:51 GMT

Do you think you can share a sample ? So that we can try and give you a better or working solution.

Re: Condition in Expression

Thu, 30 Jun 2016 13:29:52 GMT

Oh, perfect! That worked, thanks!

Re: Condition in Expression

Thu, 30 Jun 2016 13:31:41 GMT

I will look at this too. Thanks!