topic Re: Set Analysis <= expression in QlikView

Set Analysis <= expression

Tue, 28 Apr 2015 15:09:48 GMT

Hi, I have what I think is a really simple question here, but just can quite get it right. I want to use a calculation as part of my set analysis (line 3 below) but know I've got the syntax wrong on line 3

count({<UNIT.STOCKSTATUS={'sold'},
BUYER.B_NO={"*"},
UNIT.M_MILEAGE <= [Avg. Mileage]+15000
>}
UNIT.TOTALSOLD)

I'd be really grateful if anyone could point me in the right direction, thanks

Re: Set Analysis <= expression

ramoncova06 — Tue, 28 Apr 2015 15:57:07 GMT

whenever using < or > in your set analysis you have to use advanced search so instead of just being <= you have to do {"<=$(=([Avg. Mileage]+15000))"}

this expression should work

count({<UNIT.STOCKSTATUS={'sold'}, BUYER.B_NO={"*"},UNIT.M_MILEAGE = {"<=$(=([Avg. Mileage]+15000))"}

UNIT.TOTALSOLD)

Re: Set Analysis <= expression

anbu1984 — Tue, 28 Apr 2015 16:25:31 GMT

Set analysis calculates at Table level and not row level. Use If like below

count( {<UNIT.STOCKSTATUS={'sold'},BUYER.B_NO={"*"} >}

If(UNIT.M_MILEAGE <= [Avg. Mileage]+15000,UNIT.TOTALSOLD)

)

Re: Set Analysis <= expression

Wed, 29 Apr 2015 08:26:40 GMT

Hi, thanks for the suggestions.

[Avg. Mileage] is the name of an expression in the same table which is:

=avg({<UNIT.STOCKSTATUS={'sold'}>} UNIT.M_MILEAGE)

If I replace [Avg. Mileage] with a number I can get this to work

count({<UNIT.STOCKSTATUS={'sold'}, BUYER.B_NO={"*"},UNIT.M_MILEAGE ={"<=$(([Avg. Mileage] +15000))"}>} UNIT.TOTALSOLD)

but when I use [Avg. Mileage] or if I do this it doesn't work.

count({<UNIT.STOCKSTATUS={'sold'}, BUYER.B_NO={"*"},UNIT.M_MILEAGE = {"<=$(=(
avg({<UNIT.STOCKSTATUS={'sold'}>} UNIT.M_MILEAGE)
+15000))"} >} UNIT.TOTALSOLD)

Re: Set Analysis <= expression

anbu1984 — Wed, 29 Apr 2015 08:34:46 GMT

You can't do row level calculations or use name of another expr inside set analysis. You can try using If statement as in my above post

Re: Set Analysis <= expression

Wed, 29 Apr 2015 09:33:29 GMT

Hi, thanks, unfortunately when I use the expression above I just get 0's

Re: Set Analysis <= expression

timsaddler — Wed, 29 Apr 2015 09:49:17 GMT

Hello

Try calculating ={"<=$(([Avg. Mileage] +15000))"}>} as a "variable" and then introducing that into the set analysis?

Re: Set Analysis <= expression

anbu1984 — Wed, 29 Apr 2015 09:52:38 GMT

Can you post sample app