Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

13 Replies
vishsaggi
Champion III
Champion III

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,

Anonymous
Not applicable
Author

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

vishsaggi
Champion III
Champion III

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.

Anonymous
Not applicable
Author

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

vishsaggi
Champion III
Champion III

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

Capture.PNG

Anonymous
Not applicable
Author

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

vishsaggi
Champion III
Champion III

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 stalwar1neelamsaroha1575marcowedel

sunny_talwar

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

Anonymous
Not applicable
Author

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