topic Re: Aggregation problem in QlikView

Aggregation problem

maleksafa — Tue, 24 Mar 2015 14:37:58 GMT

Hello,

i have a problem that i am trying to fix related to Aggr

the expression is the below:

sum({<

H_Flag={'Tickets per hour'},

[Transaction Date] = {">=$(=min({<H_Flag={'PeopleCount'}>} [Transaction Date]))"}

>}TIK_TickCount)

what basically i am trying to do is to sum the filed 'TIK_TickCount' where the Transaction Date is greater than the minimum Transaction date of the Flag 'PeopleCount'.

the problem is that the where condition is not being calculated for each dimension. in the below case i have two stores selected and you can see in the second expression it is returning the correct date per store, but when used in the first expression it is only returning the minimum value which is 02-Feb-2014 for both stores.

Re: Aggregation problem

MK_QSL — Tue, 24 Mar 2015 14:46:18 GMT

Provide few lines of sample data please

Re: Aggregation problem

jonathandienst — Tue, 24 Mar 2015 14:48:12 GMT

Set expressions are evaluated globally, outside the context of the dimensions, so you cannot use set expressions for what you are tryng to achieve. You have two options:

Use a Sum(If()) type expression. This can be a performance killer in large data sets.
Create flag during load (set 1 when condition is true) and use the flag in a set expression. This is possibly less flexible that the sum(if()) option, but should perform far better.

Which option depends on what you are trying to achieve. The script option needs to be done at the level of granularity into which your users will want to drill.

HTH

Jonathan

Re: Aggregation problem

maleksafa — Tue, 24 Mar 2015 14:58:38 GMT

Sum(If()) is definitely a performace killer in my case.

can't i use some sort of aggr() function so that the expression related to date (=min({<H_Flag={'PeopleCount'}>} [Transaction Date]))) will be evaluated for each store (dimension)?

Re: Aggregation problem

maleksafa — Tue, 24 Mar 2015 15:23:03 GMT

Hi,

Please find attached a sample application

Re: Aggregation problem

jonathandienst — Wed, 25 Mar 2015 05:59:37 GMT

Add this to your load script:

Join(Data)

LOAD Store,

Min([Transaction Date]) As MinDate

Resident Data

Where H_Flag = 'PeopleCount'

Group By Store;

Join (Data)

LOAD Distinct Store,

[Transaction Date],

If([Transaction Date] >= MinDate, 1, 0) As MinFlag

Resident Data;

Now in a chart/table with Store as the dimension:

=sum({<H_Flag={'Tickets per hour'}, MinFlag={1}>} TIK_TickCount)

HTH

Jonathan

Re: Aggregation problem

Anonymous — Wed, 25 Mar 2015 06:18:04 GMT

Try below for min of people count Transaction date expression

">=$(=date(min(Total{<H_Flag={'PeopleCount'}>} [Transaction Date])))"

Re: Aggregation problem

MK_QSL — Wed, 25 Mar 2015 07:17:42 GMT

As already explained by Jonathan, Set Analysis will evaluate set expression globally not per line. so you need to work around your script or use If function.

Please check enclosed file.... Similar to what Jonathan has answered.