Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there!
I have the following data set with multiple regions and company names. I'm able to accumulate the Activity column from January to the maximum month, but I have trouble multiplying the accumulated Activity by the Percent from the maximum Period
Period | Region | Name | Percent | Correct Percent | Activity |
---|---|---|---|---|---|
Jan 2015 | A | XYZ Company | 9 | 11 | 100 |
Feb 2015 | A | XYZ Company | 9 | 11 | 2 |
Mar 2015 | A | XYZ Company | 10 | 11 | 3 |
Apr 2015 | A | XYZ Company | 10 | 11 | 1 |
May 2015 | A | XYZ Company | 10 | 11 | 5 |
Jun 2015 | A | XYZ Company | 11 | 11 | 5 |
Jul 2015 | A | XYZ Company | 11 | 11 | 1 |
A few examples..
For Period Mar 2015, the cumulative Activity of 105 should be multiplied by 10%
For Period May 2015, the cumulative Activity of 111 should be multiplied by 10%
For Period Jun 2015, the cumulative Activity of 116 should be multiplied by 11%
TL;DR
Accumulate the Activity column from January to the max month and then multiply it by the Percent from the max month.
Test:
LOAD Date#(Period,'MMM YYYY') as Period,
Region,
Name,
Percent,
[Correct Percent],
Activity
FROM
[https://community.qlik.com/thread/176374]
(html, codepage is 1252, embedded labels, table is @1);
NoConcatenate
Final:
Load *, NewField/Percent as Final;
Load
*,
Percent as P1,
If(RowNo() = 1, Activity,
RangeSum(Peek('NewField'),Activity)) as NewField
Resident Test
Order By Period;
Drop Table Test;
Drop Field NewField;
Test:
LOAD Date#(Period,'MMM YYYY') as Period,
Region,
Name,
Percent,
[Correct Percent],
Activity
FROM
[https://community.qlik.com/thread/176374]
(html, codepage is 1252, embedded labels, table is @1);
NoConcatenate
Final:
Load *, NewField/Percent as Final;
Load
*,
Percent as P1,
If(RowNo() = 1, Activity,
RangeSum(Peek('NewField'),Activity)) as NewField
Resident Test
Order By Period;
Drop Table Test;
Drop Field NewField;
Check this -
Hi Manish,
Thanks for your response. I'm looking to solve this problem with an expression if it's possible. I've attached an example of the current problem I have which should help explain the problem better.
Hi Digvijay,
Thanks for your reply! I've attached an example of the problem I have. Perhaps this could be solved with an expression instead of the script?
So for example if I select Year = 2015, and Month = March, then XYZ Company's result should show me 10.5
Check this out, I have PE so cannot access qvw of yours..
maybe
rangesum(Above(sum(Activity),0,RowNo())) * Percent/100
rangesum(Above(sum(Activity),0,RowNo(Total))) / Percent
Hi Manesh!
It looks like your first solution works best so far. I'm hoping to expand it further to this example where we accumulate Activity by Company Name from January to December of each year. But Account Number has to be grouped by three separate pairs: 1 & 2, 11 & 12, and 21 & 22. Is that possible through the script?
Period | Name | Account Number | Percent | Activity |
1/1/2014 | XYZ Company | 1 | 9 | 100 |
1/1/2014 | XYZ Company | 2 | 9 | 100 |
1/1/2014 | XYZ Company | 11 | 9 | 50 |
1/1/2014 | XYZ Company | 12 | 9 | 50 |
1/1/2014 | XYZ Company | 21 | 9 | 25 |
1/1/2014 | XYZ Company | 22 | 9 | 25 |
1/1/2014 | ABC Company | 1 | 9 | 1 |
1/1/2014 | ABC Company | 2 | 9 | 1 |
1/1/2014 | ABC Company | 11 | 9 | 5 |
1/1/2014 | ABC Company | 12 | 9 | 5 |
1/1/2014 | ABC Company | 21 | 9 | 2 |
1/1/2014 | ABC Company | 22 | 9 | 2 |
2/1/2014 | XYZ Company | 1 | 9 | 1 |
2/1/2014 | XYZ Company | 2 | 9 | 1 |
2/1/2014 | XYZ Company | 11 | 9 | 5 |
2/1/2014 | XYZ Company | 12 | 9 | 5 |
2/1/2014 | XYZ Company | 21 | 9 | 2 |
2/1/2014 | XYZ Company | 22 | 9 | 2 |
2/1/2014 | ABC Company | 1 | 9 | 1 |
2/1/2014 | ABC Company | 2 | 9 | 1 |
2/1/2014 | ABC Company | 11 | 9 | 5 |
2/1/2014 | ABC Company | 12 | 9 | 5 |
2/1/2014 | ABC Company | 21 | 9 | 2 |
2/1/2014 | ABC Company | 22 | 9 | 2 |
3/1/2014 | XYZ Company | 1 | 9 | 1 |
3/1/2014 | XYZ Company | 2 | 9 | 1 |
3/1/2014 | XYZ Company | 11 | 9 | 5 |
3/1/2014 | XYZ Company | 12 | 9 | 5 |
3/1/2014 | XYZ Company | 21 | 9 | 2 |
3/1/2014 | XYZ Company | 22 | 9 | 2 |
3/1/2014 | ABC Company | 1 | 9 | 1 |
3/1/2014 | ABC Company | 2 | 9 | 1 |
3/1/2014 | ABC Company | 11 | 9 | 5 |
3/1/2014 | ABC Company | 12 | 9 | 5 |
3/1/2014 | ABC Company | 21 | 9 | 2 |
3/1/2014 | ABC Company | 22 | 9 | 2 |
4/1/2014 | XYZ Company | 1 | 9 | 1 |
4/1/2014 | XYZ Company | 2 | 9 | 1 |
4/1/2014 | XYZ Company | 11 | 9 | 5 |
4/1/2014 | XYZ Company | 12 | 9 | 5 |
4/1/2014 | XYZ Company | 21 | 9 | 2 |
4/1/2014 | XYZ Company | 22 | 9 | 2 |
4/1/2014 | ABC Company | 1 | 9 | 1 |
4/1/2014 | ABC Company | 2 | 9 | 1 |
4/1/2014 | ABC Company | 11 | 9 | 5 |
4/1/2014 | ABC Company | 12 | 9 | 5 |
4/1/2014 | ABC Company | 21 | 9 | 2 |
4/1/2014 | ABC Company | 22 | 9 | 2 |
5/1/2014 | XYZ Company | 1 | 9 | 1 |
5/1/2014 | XYZ Company | 2 | 9 | 1 |
5/1/2014 | XYZ Company | 11 | 9 | 5 |
5/1/2014 | XYZ Company | 12 | 9 | 5 |
5/1/2014 | XYZ Company | 21 | 9 | 2 |
5/1/2014 | XYZ Company | 22 | 9 | 2 |
5/1/2014 | ABC Company | 1 | 9 | 1 |
5/1/2014 | ABC Company | 2 | 9 | 1 |
5/1/2014 | ABC Company | 11 | 9 | 5 |
5/1/2014 | ABC Company | 12 | 9 | 5 |
5/1/2014 | ABC Company | 21 | 9 | 2 |
5/1/2014 | ABC Company | 22 | 9 | 2 |
6/1/2014 | XYZ Company | 1 | 9 | 1 |
6/1/2014 | XYZ Company | 2 | 9 | 1 |
6/1/2014 | XYZ Company | 11 | 9 | 5 |
6/1/2014 | XYZ Company | 12 | 9 | 5 |
6/1/2014 | XYZ Company | 21 | 9 | 2 |
6/1/2014 | XYZ Company | 22 | 9 | 2 |
6/1/2014 | ABC Company | 1 | 9 | 1 |
6/1/2014 | ABC Company | 2 | 9 | 1 |
6/1/2014 | ABC Company | 11 | 9 | 5 |
6/1/2014 | ABC Company | 12 | 9 | 5 |
6/1/2014 | ABC Company | 21 | 9 | 2 |
6/1/2014 | ABC Company | 22 | 9 | 2 |
7/1/2014 | XYZ Company | 1 | 9 | 1 |
7/1/2014 | XYZ Company | 2 | 9 | 1 |
7/1/2014 | XYZ Company | 11 | 9 | 5 |
7/1/2014 | XYZ Company | 12 | 9 | 5 |
7/1/2014 | XYZ Company | 21 | 9 | 2 |
7/1/2014 | XYZ Company | 22 | 9 | 2 |
7/1/2014 | ABC Company | 1 | 9 | 1 |
7/1/2014 | ABC Company | 2 | 9 | 1 |
7/1/2014 | ABC Company | 11 | 9 | 5 |
7/1/2014 | ABC Company | 12 | 9 | 5 |
7/1/2014 | ABC Company | 21 | 9 | 2 |
7/1/2014 | ABC Company | 22 | 9 | 2 |
8/1/2014 | XYZ Company | 1 | 9 | 1 |
8/1/2014 | XYZ Company | 2 | 9 | 1 |
8/1/2014 | XYZ Company | 11 | 9 | 5 |
8/1/2014 | XYZ Company | 12 | 9 | 5 |
8/1/2014 | XYZ Company | 21 | 9 | 2 |
8/1/2014 | XYZ Company | 22 | 9 | 2 |
8/1/2014 | ABC Company | 1 | 9 | 1 |
8/1/2014 | ABC Company | 2 | 9 | 1 |
8/1/2014 | ABC Company | 11 | 9 | 5 |
8/1/2014 | ABC Company | 12 | 9 | 5 |
8/1/2014 | ABC Company | 21 | 9 | 2 |
8/1/2014 | ABC Company | 22 | 9 | 2 |
9/1/2014 | XYZ Company | 1 | 9 | 1 |
9/1/2014 | XYZ Company | 2 | 9 | 1 |
9/1/2014 | XYZ Company | 11 | 9 | 5 |
9/1/2014 | XYZ Company | 12 | 9 | 5 |
9/1/2014 | XYZ Company | 21 | 9 | 2 |
9/1/2014 | XYZ Company | 22 | 9 | 2 |
9/1/2014 | ABC Company | 1 | 9 | 1 |
9/1/2014 | ABC Company | 2 | 9 | 1 |
9/1/2014 | ABC Company | 11 | 9 | 5 |
9/1/2014 | ABC Company | 12 | 9 | 5 |
9/1/2014 | ABC Company | 21 | 9 | 2 |
9/1/2014 | ABC Company | 22 | 9 | 2 |
10/1/2014 | XYZ Company | 1 | 9 | 1 |
10/1/2014 | XYZ Company | 2 | 9 | 1 |
10/1/2014 | XYZ Company | 11 | 9 | 5 |
10/1/2014 | XYZ Company | 12 | 9 | 5 |
10/1/2014 | XYZ Company | 21 | 9 | 2 |
10/1/2014 | XYZ Company | 22 | 9 | 2 |
10/1/2014 | ABC Company | 1 | 9 | 1 |
10/1/2014 | ABC Company | 2 | 9 | 1 |
10/1/2014 | ABC Company | 11 | 9 | 5 |
10/1/2014 | ABC Company | 12 | 9 | 5 |
10/1/2014 | ABC Company | 21 | 9 | 2 |
10/1/2014 | ABC Company | 22 | 9 | 2 |
11/1/2014 | XYZ Company | 1 | 9 | 1 |
11/1/2014 | XYZ Company | 2 | 9 | 1 |
11/1/2014 | XYZ Company | 11 | 9 | 5 |
11/1/2014 | XYZ Company | 12 | 9 | 5 |
11/1/2014 | XYZ Company | 21 | 9 | 2 |
11/1/2014 | XYZ Company | 22 | 9 | 2 |
11/1/2014 | ABC Company | 1 | 9 | 1 |
11/1/2014 | ABC Company | 2 | 9 | 1 |
11/1/2014 | ABC Company | 11 | 9 | 5 |
11/1/2014 | ABC Company | 12 | 9 | 5 |
11/1/2014 | ABC Company | 21 | 9 | 2 |
11/1/2014 | ABC Company | 22 | 9 | 2 |
12/1/2014 | XYZ Company | 1 | 9 | 1 |
12/1/2014 | XYZ Company | 2 | 9 | 1 |
12/1/2014 | XYZ Company | 11 | 9 | 5 |
12/1/2014 | XYZ Company | 12 | 9 | 5 |
12/1/2014 | XYZ Company | 21 | 9 | 2 |
12/1/2014 | XYZ Company | 22 | 9 | 2 |
12/1/2014 | ABC Company | 1 | 9 | 1 |
12/1/2014 | ABC Company | 2 | 9 | 1 |
12/1/2014 | ABC Company | 11 | 9 | 5 |
12/1/2014 | ABC Company | 12 | 9 | 5 |
12/1/2014 | ABC Company | 21 | 9 | 2 |
12/1/2014 | ABC Company | 22 | 9 | 2 |
1/1/2015 | XYZ Company | 1 | 9 | 100 |
1/1/2015 | XYZ Company | 2 | 9 | 100 |
1/1/2015 | XYZ Company | 11 | 9 | 50 |
1/1/2015 | XYZ Company | 12 | 9 | 50 |
1/1/2015 | XYZ Company | 21 | 9 | 25 |
1/1/2015 | XYZ Company | 22 | 9 | 25 |
1/1/2015 | ABC Company | 1 | 9 | 1 |
1/1/2015 | ABC Company | 2 | 9 | 1 |
1/1/2015 | ABC Company | 11 | 9 | 5 |
1/1/2015 | ABC Company | 12 | 9 | 5 |
1/1/2015 | ABC Company | 21 | 9 | 2 |
1/1/2015 | ABC Company | 22 | 9 | 2 |
2/1/2015 | XYZ Company | 1 | 9 | 1 |
2/1/2015 | XYZ Company | 2 | 9 | 1 |
2/1/2015 | XYZ Company | 11 | 9 | 5 |
2/1/2015 | XYZ Company | 12 | 9 | 5 |
2/1/2015 | XYZ Company | 21 | 9 | 2 |
2/1/2015 | XYZ Company | 22 | 9 | 2 |
2/1/2015 | ABC Company | 1 | 9 | 1 |
2/1/2015 | ABC Company | 2 | 9 | 1 |
2/1/2015 | ABC Company | 11 | 9 | 5 |
2/1/2015 | ABC Company | 12 | 9 | 5 |
2/1/2015 | ABC Company | 21 | 9 | 2 |
2/1/2015 | ABC Company | 22 | 9 | 2 |
3/1/2015 | XYZ Company | 1 | 9 | 1 |
3/1/2015 | XYZ Company | 2 | 9 | 1 |
3/1/2015 | XYZ Company | 11 | 9 | 5 |
3/1/2015 | XYZ Company | 12 | 9 | 5 |
3/1/2015 | XYZ Company | 21 | 9 | 2 |
3/1/2015 | XYZ Company | 22 | 9 | 2 |
3/1/2015 | ABC Company | 1 | 9 | 1 |
3/1/2015 | ABC Company | 2 | 9 | 1 |
3/1/2015 | ABC Company | 11 | 9 | 5 |
3/1/2015 | ABC Company | 12 | 9 | 5 |
3/1/2015 | ABC Company | 21 | 9 | 2 |
3/1/2015 | ABC Company | 22 | 9 | 2 |