Aggregation problem

[maleksafa]

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.

[MK_QSL]

Provide few lines of sample data please

[jonathandienst · Accepted solution]

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

[maleksafa]

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)?

[maleksafa]

Hi,

Please find attached a sample application

[jonathandienst]

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

[Anonymous]

Hi

Try below for min of people count Transaction date expression

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

[MK_QSL]

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.