Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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,
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
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.
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
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))
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
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
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))
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