topic Re: $-sign Calculated Dimension syntax with condition in QlikView

$-sign Calculated Dimension syntax with condition

Wed, 16 Jul 2014 07:11:47 GMT

I have a pivot-table with one calculated dimension of this form

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

IF([Corporate Account]='OTHER',[Sales Account],

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

If I test alone the field on which i aggregate,

IF([Corporate Account]='OTHER',[Sales Account],

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...)))

this is the right computation.

However,

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

IF([Corporate Account]='OTHER',[Sales Account],

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

gives Null() for all rows.

On the contrary,

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

$(=IF([Corporate Account]='OTHER',[Sales Account],

pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

gives some results, but result is wrong: it is as if the IF in the condition in the $-sign expansion was never hit, whereas there are cases where Corporate Account is 'OTHER'

Would you have a hint ? How to fix this ?

Thanks in advance.

Re: $-sign Calculated Dimension syntax with condition

hic — Wed, 16 Jul 2014 07:21:20 GMT

The $-expansion is made on a global level; before the chart is expanded, i.e. not once per record. This means that

$(=IF([Corporate Account]='OTHER', ...

isn't evaluated the way you want.

Bottom line is that you cannot use $-expansions this way.

HIC

Re: $-sign Calculated Dimension syntax with condition

Wed, 16 Jul 2014 07:25:31 GMT

thanks a lot

but what about

=Aggr( Concat(IF([Product Type]= 'Sales',IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year))),','),

IF([Corporate Account]='OTHER',[Sales Account],

$(=pick(1+match('[Corporate Account]',...,...,...),...,...,...,...))))

? should be accurate, and indeed the $(=) can here be evaluated globally

Re: $-sign Calculated Dimension syntax with condition

Wed, 16 Jul 2014 07:28:35 GMT

Is it that generally speaking the field upon which aggregation is made in AGGR() is evaluated globally, and not by rows?

Re: $-sign Calculated Dimension syntax with condition

marcus_sommer — Wed, 16 Jul 2014 07:54:03 GMT

Aggr is a normal function and will be evaluted by row and how Henric Cronström hint is $(=Expression) a variables which are always global - therefore try to simplify your approaches, perhaps with additionally flag-fields within the script or use Refer to Another Dimension in a Dimension in Pivot Table

- Marcus

Re: $-sign Calculated Dimension syntax with condition

hic — Wed, 16 Jul 2014 08:52:43 GMT

The Aggr() is not normal function. It is more like a For-Next loop, that creates an array of values.

The first parameter of the Aggr() function will be evaluated per distinct value of the second (and third) parameter. Hence, the first parameter must be an aggregation function (e.g.Sum(), Count(), Min(), Max(), Concat(), ...).

Example:

Aggr( Count(distinct OrderID), CustomerID )

will create a dimension with the the number of orders per customer: 0, 1, 2, 3, etc. The count function will be evaluated once per customer.

See more on Aggregations and Function Classes.

HIC

Re: $-sign Calculated Dimension syntax with condition

Wed, 16 Jul 2014 08:56:47 GMT

Thanks a lot!

What about this: I am aggregation concat(), which sounds reasonable. But I am appending the 'Year' only under some conditions.

Aggr( Concat( IF([Product Type]= 'Sales', IF(MasterCodeDesc = 'BASE', IF(Year>2009,Year,'None'),'None'),'None'),','),

$(=pick(1+match(...,...,...),...,...,...)))

this is however not working. is the syntax correct?

Re: $-sign Calculated Dimension syntax with condition

marcus_sommer — Wed, 16 Jul 2014 09:02:05 GMT

There is again $(=pick(1+match(...,...,...),...,...,...)) which wouldn't probably return a valid Field(list) and if it did then it is the same for all rows.

- Marcus

Re: $-sign Calculated Dimension syntax with condition

Wed, 16 Jul 2014 09:05:03 GMT

thanks

but, I tested separately as another calculated dimension

=IF([Corporate Account]='OTHER',[Sales Account],$(=pick(1+match('[Corporate Account]',...,...),...,...)

and this is working fine...

so it seems to me that the $(=) is evaluated correctly...

Re: $-sign Calculated Dimension syntax with condition

Wed, 16 Jul 2014 09:17:19 GMT

Could you explain what

Aggr( Concat (IF...

is actually doing, as I wrote in the precedent post ?

Thanks in advance

Re: $-sign Calculated Dimension syntax with condition

marcus_sommer — Wed, 16 Jul 2014 10:11:44 GMT

That an expression could be interpreted as calculated dimension or as single expression didn't meant that these expression returned a valid value as nested part from another expression - such things could work but you couldn't be sure without expensive testings.

It's not recommended to use such complicated structures within objects - although they may work - then the time exposure for creating, validation and maintance is compared with script-solutions (which simplify calculations and are reusable in other apps and/or objects) often too high.

I couldn't imagine what you are want to reach with your pick(match()) if it not returned the dimension-level from the row - for this could be dimensionality() used .

- Marcus