Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have issue in accumulation at back end, when I am loading negative values, I am unable to get desired output.
Below are the script, qlikview output and desired output.
Kindly suggest.
Script:
LOAD * INLINE [
A,B,C,D, Sales
1,q,r,e, 200
1,q,w,e, -300
3,p,o,i,400
4,p,k,j,100
4,p,k,j,-900
5,v,b,n,-200
6,e,d,x, 200
];
CC:
LOAD
*,
Rangesum(Sales,Peek('CumSales')) as CumSales
Resident BB;
DROP Table BB;
Qlikview output:
Desired Output:
A | Sum(Sales) | Sum(CumSales) |
1 | -100 | -100 |
3 | 400 | 300 |
4 | -800 | -500 |
5 | -200 | -700 |
6 | 200 | -500 |
Check this
Does this you need from Script level or UI
I need this at script level and with respect to all of my dimensions. If I take one or more than one dimensions then values should be accumulate correctly.
Why you need script level, Why not Simple Full accumulation for this? Is there any link to other symptoms to show those? Can you confirm the same. Even, If you use script level what was the use?
I am working in qliksense (where is no option for accumulate values in tables) and need to map as on stock with every date. So I need accumulate values with respect to every date in SAP mseg table.
Make Sense, But why not in Qlik sense Front-End side
I used Dimension as A
Expressions are
1) Sum(Sales)
2) Rangesum(Below(Sum(Sales),0),Above(Sales,1,10))
I agree with Anil that it might make sense to do this on the front end unless you don't want to filter your accumulation based on other attributes. To simplify Anil's solution a little, you can use this:
RangeSum(Above(Sum(Sales),0, RowNo()))
I have already tried this but I need in script. Because I need stock as of an specific date which can only be calculated from script. If here in front end when you will select any specific date then it will show only the entries of that date, not accumulation.
I have tried accumulation in script, and that also giving me desired values (when all values are positive), but here I also have negative values..
I need solution at urgent basis. Please provide if you got.
Thanks in advance.
With regards to the date issues you mentioned, those can be resolved using set analysis. But if you still insist, try like this:
Table:
LOAD * INLINE [
A,B,C,D, Sales
1,q,r,e, 200
1,q,w,e, -300
3,p,o,i,400
4,p,k,j,100
4,p,k,j,-900
5,v,b,n,-200
6,e,d,x, 200
];
LinkTable:
LOAD A,
RangeSum(Peek('CumSales'), SumSales) as CumSales;
LOAD A,
Sum(Sales) as SumSales
Resident Table
Group By A
Order By A;
Best,
Sunny
Thanks for your valuable time.
But I have also tired this method. I have many dimensions and I need accumulated data with respect to all of the dimensions.
If I select B as dimension in table then it also should be accumulate properly.