topic Re: Is it possible to group an aggregate function by aggr calculated dimension in QlikView

Is it possible to group an aggregate function by aggr calculated dimension

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

Hello,

I am trying to have a cumulative total of SKU issues per manufacturing year in a pivot table where "Year" followed by "Min_Lag" are my dimensions. So far I got it to work with the first formula(see below), however I am curious to know if I can replace the last argument "Min_Lag" with an aggregate function that acts just like my Min_Lag column in my data set. Formula 1 uses a table which is added to the script, I am trying to avoid this so I don't double the size of my data.

1) sum( aggr( rangesum( above( count(DISTINCT[SKU]),0,12) ),[Year], Min_Lag))

2) sum( aggr( rangesum( above( count(DISTINCT[SKU]),0,12) ),[Year], aggr(min(Lag),[SKU],[Year]))

Can Min_Lag be replaced with aggr(min(Lag),[SKU],[Year])?

Thanks

Re: Is it possible to group an aggregate function by aggr calculated dimension

vishsaggi — Thu, 30 Aug 2018 14:19:01 GMT

What is that you are trying to achieve with your second expression? Is your dimension name min_lag or just lag? Min is an aggr function but you mentioned in your dimension min_lag as your field name? Confused,

Re: Is it possible to group an aggregate function by aggr calculated dimension

Anonymous — Thu, 30 Aug 2018 14:26:54 GMT

Hi Vishwarath,

Thank you for your response.

min_lag is a field which was hard coded into the script. It is another column which takes the minimum of the lag and it is grouped by SKU. For example

SKU | Lag | Min Lag

1234| 0 | 0

1235| 0 | 0

1236| 1 | 1

1236| 2 | 1

1237| 1 | 1

1237| 4 | 1

My goal is to avoid hard coding it into the script and create it as a virtual table when necessary with aggr() function

Re: Is it possible to group an aggregate function by aggr calculated dimension

vishsaggi — Thu, 30 Aug 2018 18:40:29 GMT

I doubt you can create an Aggr(Min(Lag),) in your Aggr field list like you mentioned. Why don't you create a calculated dimension like

= Aggr(Min(Lag), SKU, YEAR)

OR in your expr like

= Min(TOTAL <SKU, YEAR> Lag)

If none of these does not help share your actual qvw file with expected output in a table will look into it.

Re: Is it possible to group an aggregate function by aggr calculated dimension

Anonymous — Thu, 30 Aug 2018 19:06:26 GMT

Hello Vishwarath,

Very grateful for your time and patience.

Currently I am reading from a csv file that has 3 relevant fields

Load

YEAR,

SKU,

LAG,

min(LAG) as Min_Lag -- this is the part that I have to hard code

Year, SKU, LAG, Min_Lag

2011| 1234 | 1 | 1

2011| 1234 | 2 | 1

2012| 1235 | 3 | 3

2012| 1236 | 4 | 4

2011| 1234 | 5 | 1

2014| 1237 | 3 | 3

2015| 1238 | 3 | 3

Now I have a pivot table with 2 dimensions

YEAR

LAG

with an expression that says

sum( aggr( rangesum( above( count(DISTINCT[SKU]),0,6) ),[YEAR], Min_Lag))

What I see is

1 2 3 4 5

________________

2011 |1 1 1 1 1

2012 |0 0 1 2

2014 |0 0 1

2015 |0 0 1

(Note as a check the sum of the last columns is the total number of unique SKU's that I have (1+2+1+1 = 5 unique SKU'S) )

Which is what I want. However, I am curious to know if the hard coded Min_Lag which is used in my expression

can be replaced with some other function such as aggr() which would act similar to that field.

Hope this is clear.

Regards,

Anand

Re: Is it possible to group an aggregate function by aggr calculated dimension

vishsaggi — Thu, 30 Aug 2018 19:36:19 GMT

Try this? So the Min_Lag will be dynamic when you have the data populated in SKU table.

SKU:

LOAD * INLINE [

Year| SKU| LAG

2011| 1234 | 1

2011| 1234 | 2

2012| 1235 | 3

2012| 1236 | 4

2011| 1234 | 5

2014| 1237 | 3

2015| 1238 | 3

] (delimiter is '|');

LEFT JOIN(SKU)

LOAD SKU,

Year,

Min(LAG) AS Min_Lag

Resident SKU

Group By SKU, Year;

using Pivot table add like below

Dim: Year, LAG

Expr: = Sum(aggr(above(count(DISTINCT SKU),0), Year, Min_Lag))

Re: Is it possible to group an aggregate function by aggr calculated dimension

Anonymous — Thu, 30 Aug 2018 19:52:50 GMT

Hi Vishwarath,

Your help is extremely valuable.

My goal is to figure out a way to work around having to reload the script to add the new MIN_LAG dimension.

The solution that you presented is one that I am currently using (On a smaller sample data set). However I am working on another QV file that only has YEAR, SKU, LAG but not Min_Lag.

This new QV file can't be reloaded since I don't have access to the files that are being read. Thus I am trying to discover a sneaky way around this by replacing the variable Min_Lag in the expression with something of similar structure.

I hope this was clear,

Thanks again

Re: Is it possible to group an aggregate function by aggr calculated dimension

vishsaggi — Thu, 30 Aug 2018 20:35:01 GMT

Ohh ok. Well I doubt if we can do that dynamic aggr inside an aggr function we receive an error in expression. However, lets ask some help from stalwar1‌ neelamsaroha1575‌ marcowedel‌

Re: Is it possible to group an aggregate function by aggr calculated dimension

sunny_talwar — Fri, 31 Aug 2018 02:10:15 GMT

I am not 100% sure, but can you not use SKU instead of min_lag?

Sum(Aggr(RangeSum(Above(Count(DISTINCT [SKU]), 0, 6)), [YEAR], SKU))

Re: Is it possible to group an aggregate function by aggr calculated dimension

Anonymous — Fri, 31 Aug 2018 03:23:01 GMT

Sunny thank you for your respone,

Swapping SKU and YEAR almost did the trick for me.

Sum(Aggr(RangeSum(Above(Count(DISTINCT [SKU]), 0, 6)), SKU, [YEAR]).

Now this gives me an incremental pivot table across the rows, is there anyway I can make this cumulative?

for example I am seeing

1 2 3 4 5

2011| 10 2 7 0 2

2012| 2 0 1 2 1

2013| 6 5 8 9 0

2014| 2 7 1 0 0

2015| 5 0 0 0 0

what I want is

1 2 3 4 5

2011| 10 12 19 19 21

2012| 2 2 3 5 6

2013| 6 11 19 28 28

2014| 2 9 10 10 10

2015| 5 5 5 5 5

Thank you everyone for your thoughtful contributions,

Anand

Re: Is it possible to group an aggregate function by aggr calculated dimension

sunny_talwar — Fri, 31 Aug 2018 11:09:33 GMT

It is difficult to know without a sample... but try this

RangeSum(Before(Sum(Aggr(RangeSum(Above(Count(DISTINCT [SKU]), 0, 6)), SKU, [YEAR]), 0, ColumnNo()))

Re: Is it possible to group an aggregate function by aggr calculated dimension

sunny_talwar — Fri, 31 Aug 2018 11:15:40 GMT

Or how about this may be

RangeSum(Before(Sum(Aggr(Count(DISTINCT SKU), YEAR, SKU)), 0, 6))

Re: Is it possible to group an aggregate function by aggr calculated dimension

Anonymous — Fri, 31 Aug 2018 12:58:27 GMT

The second option was nice solution! I swapped the 6 in the last argument with the LAG dimension since it can definitely vary.

Thank you Sunny Talwar for your time an effort

Re: Is it possible to group an aggregate function by aggr calculated dimension

sunny_talwar — Fri, 31 Aug 2018 13:02:55 GMT

No problem, I am glad we were able to help.