Artificial Dimensions or/and Set Analysis Expression

Sat, 16 Jun 2012 20:35:34 GMT

Hey all,

i have a very strange problem with my expression - it counts properly only in some cases, not all the time.

for example if Y = 0, it counts properly,

and if i selected some lines within a listbox, it counts also properly.

But if i change the Y value , it counts wrong. i've checked it with my sql statement.

The expression is:

= count( if (RELGEWICHT>=X and aggr( noDISTINCT Count(FALLNR), DRG3)>=Y and [AUFNGRUND ZUSATZ]= '07', 1))

The SQL Statement i'm using to compare the results is like that :

Temp_Table:

select DRG3, COUNT(*) as Number

from Data_Table

where [AUFNGRUND ZUSATZ] = '07'

and RELGEWICHT>=X

group by DRG)

having COUNT (DRG3)>=20

)

select SUM(Number)

from temp_table

As you can see, all i want is to count the number of lines , wich have following properties:

a) RELGEWICHT>=X

b) [AUFNGRUND ZUSATZ]= '07'

c) that lines are only in scope if we have Y or more lines of that kind, see having clause in SQL and aggr() in the expression

Can anyone help, please ?

Regards, alex

Re: Artificial Dimensions or/and Set Analysis Expression

Anonymous — Mon, 18 Jun 2012 03:53:27 GMT

Hi Alexey,

Tricky to answer this one without any application to test on

Here's an expression that might be close to what you're trying to do:

Sum({$<RELGEWICHT={">X"}, [AUFNGRUND ZUSATZ]={'07'}>} if(Aggr(Count(FALLNR),DRG3)>=Y,Aggr(Count(FALLNR),DRG3))

Using Set Analysis we set RELGEWICHT to be larger than X and AUFNGRUND ZUSATZ to 07.

Then we look at whether the aggregated count of FALLNR per DRG3 is equal to or larger than Y. If this is true, use the count and the Sum() on the outside of the expression we sum up all these counts that fall within the scope.

Haven't tested the expression in QlikView so there might be some syntax issues with the set analysis and expression logic.

topic Artificial Dimensions or/and Set Analysis Expression in QlikView

Artificial Dimensions or/and Set Analysis Expression

Re: Artificial Dimensions or/and Set Analysis Expression