Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.