topic Re: Set analyses in combination with if statement in QlikView

Set analyses in combination with if statement

francisvandergr — Tue, 01 Nov 2011 11:43:50 GMT

I have a question.

i have dimension called "Costcode" and 2 important fields called "amount" and "accountno"

What i want is when a "Costcode" is selected in my object all values of Costcode must be visible. Something like :

sum( {$<Costcode = >} Amount )

But i want only amounts from "accountno"between 3000 and 4000'.

Can anybody help me with this formula ?

Anonymous — Tue, 01 Nov 2011 11:45:45 GMT

Should be something like this ...

sum( {<Codecode=, account= {">3000<4000"} >} Amount)

Tue, 01 Nov 2011 15:17:13 GMT

Hi Francis, use this: if(accountno >= 3000 and accountno <= 4000, sum({$<Costcode = >} amount ))

Anonymous — Tue, 01 Nov 2011 15:18:59 GMT

@matytr21 -- why using an if, if this can be solved within the set analysis?

Tue, 01 Nov 2011 15:28:16 GMT

Yes, you have the reason, with set analysis is better

francisvandergr — Mon, 21 Nov 2011 18:13:31 GMT

I have made an example with data. Maybe someone has now the right formula. Iw ant in the textbox a result of 230. I attached the file

Mon, 21 Nov 2011 18:22:24 GMT

So you want it to display 230 even if the selections currently made are not between 3000 and 4000?

So you basically want to ignore the selections in name cost code.

swuehl — Mon, 21 Nov 2011 18:22:41 GMT

I think you need to clear the field [Name Costcode], you selected in, like

=sum( {<Costcode=, [Name Costcode]=, Account= {">3000<4000"} >} Amount)

Hope this helps,

Stefan