Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Artificial Dimensions or/and Set Analysis Expression

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

1 Reply
Anonymous
Not applicable
Author

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.