topic Re: AGGR and Current Selection in QlikView

AGGR and Current Selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a variable to which this formula has been assigned:

SUM(AGGR( COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient))

The initial screen shows the right count but it also changes when I change my selection criteria. How do keep it

from changing? I thought "{1}" is for the entire application.

Thanks

Re: AGGR and Current Selection

Anil_Babu_Samineni — Thu, 19 Oct 2017 13:41:05 GMT

May be use here too

SUM({1} AGGR( COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient))

Re: AGGR and Current Selection

sunny_talwar — Thu, 19 Oct 2017 13:41:16 GMT

Try this

Sum({1} Aggr(Count({1} DISTINCT Date), Date, ProcedureID, Patient))

Re: AGGR and Current Selection

its_anandrjs — Thu, 19 Oct 2017 13:41:48 GMT

It seems you need do not change in the value of expression then it is working try ths also

SUM({1} AGGR( COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient))

Re: AGGR and Current Selection

Anonymous — Thu, 19 Oct 2017 13:54:18 GMT

Thank you all.

Adding {1} to the SUM command works. So I did a quick experiment by placing the {1} at

different spots.

SUM( AGGR( COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient) )

SUM( AGGR( {1} COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient) )

SUM( {1} AGGR( COUNT( DISTINCT Date), Date, ProcedureID, Patient) )

SUM( {1} AGGR( {1} COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient) )

SUM( {1} AGGR( COUNT( {1} DISTINCT Date), Date, ProcedureID, Patient) )

The first 3 did not work while the last 2 worked. Logically speaking, I would think you

only need to have one occurrence of "{1}" perhaps at the SUM() level since that's the "outer"

most command.

Can someone explain the reason for those three lines that aren't working? Just wanted

to know how QV works.

Thanks!

Re: AGGR and Current Selection

sunny_talwar — Thu, 19 Oct 2017 13:59:17 GMT

Think of aggr as a table... so essentially this is what you have

Dimensions

Date

ProcedureID

Patient

Expression

Count(DISTINCT Date)

It will filter based on your selections, right? But if you use Count({1} DISTINCT Date) it won't.

Now if you are using this in a text box

Sum(Aggr(Count({1} DISTINCT Date), Date, ProcedureID, Patient))

the inner expression doesn't filter, but the outer one is unrestricted and will filter based on selections unless you add {1}

Re: AGGR and Current Selection

Anonymous — Thu, 19 Oct 2017 14:07:53 GMT

OK. If AGGR is a table. Then why wouldn't this work:

SUM( AGGR( {1} COUNT( DISTINCT Date), Date, ProcedureID, Patient) )

Isn't this saying for the given table (AGGR), use all the available data then do a SUM?

Re: AGGR and Current Selection

Anonymous — Thu, 19 Oct 2017 14:09:19 GMT

I am very good with writing database queries but I just need to figure out how to translate that into

QV syntax.

Re: AGGR and Current Selection

sunny_talwar — Thu, 19 Oct 2017 14:12:33 GMT

But both your inner expression and outer expression are not ignoring selections... but do you think by just adding it to Aggr() it would ignore? Also, Set analysis in Aggr() is a recent thing... so I have not played around with it a lot.... As a general rule of thumb.... if I am ignoring selections, I would add {1} to anywhere I see Avg, Min, Max, Median, Sum, Mode, Concat... etc

Re: AGGR and Current Selection

Anonymous — Thu, 19 Oct 2017 14:41:10 GMT

I will definitely keep your suggestions in mind when it comes to these things.

Maybe it wouldn't hurt by placing a "{1}" in every set type operations. The 4th line up there worked for me.

Re: AGGR and Current Selection

sunny_talwar — Thu, 19 Oct 2017 14:47:08 GMT

It seems that pcammaert‌ is responding to some questions and he might be a good person to take advice from.