1 Reply Latest reply: Jun 17, 2012 11:53 PM by Johannes Sunden RSS

Artificial Dimensions or/and Set Analysis Expression

alexey.khomsky

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
    Johannes Sunden

    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.