topic Re: Aggr Function in Straight table on another expression value by different Dimension in QlikView

Aggr Function in Straight table on another expression value by different Dimension

Anonymous — Wed, 08 Jul 2015 05:37:26 GMT

Hi All,

Can some body advice me can we use Aggr function on another expression value within straight table by different dimension.

Here it my usecase.

My Input:

My Expected Straight table O/P :

Here i am trying to aggregate Column(Amt) value by Product, Quarter and expected value is same as "AGGR at Quarter" expression.

but instead of Aggr(Sum(AMt),Product,Quarter) i used Aggr(Sum(Column(1)),Product,Quarter).

Can you some body let us know is it possible to reference column name in Aggr function?

Attached sample App.

lironbaram — Wed, 08 Jul 2015 06:14:49 GMT

to the best of my knowledge you can't

use column function inside aggr ,

and generally you can't use an expression result in one table in another table

Kushal_Chawda — Wed, 08 Jul 2015 06:31:21 GMT

Sample app is not attached

Anonymous — Wed, 08 Jul 2015 06:38:42 GMT

Hi Kush,

Attached sample app.

Kushal_Chawda — Wed, 08 Jul 2015 06:50:17 GMT

Use Sum(total <Product,Quarter>AMt)

This is what you are looking for? See the attached

Anonymous — Wed, 08 Jul 2015 06:55:53 GMT

Hi Kush,

Thanks for looking

In Mycase i wanted to use Column(1) or Expression Name instead of Actual field "AMt" because in real scenario Amt is derived value.

Kushal_Chawda — Wed, 08 Jul 2015 07:02:36 GMT

So you can do that calculation in script using If else and then use that Amout

Kushal_Chawda — Wed, 08 Jul 2015 07:07:53 GMT

How amount is derived ?

Anonymous — Wed, 08 Jul 2015 07:21:24 GMT

Thanks you all.

I am able to achieve with below expression.

If( QuarterStart(MakeDate(Year,Month)) = MakeDate(Year,Month),RangeSum(Below(Column(27),0,3)), Above(Column(30)))

Basically i am checking Quarter Start and if it matches then using RangeSum function with below function to aggregate forward 3 records amount.

Thanks,