Applying Value from Max Date

[jdmarlin]

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.

[MK_QSL · Accepted solution]

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;

[Digvijay_Singh]

Check this -

[jdmarlin]

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.

[jdmarlin]

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

[Digvijay_Singh]

Check this out, I have PE so cannot access qvw of yours..

[maxgro]

maybe

rangesum(Above(sum(Activity),0,RowNo())) * Percent/100

[MK_QSL]

rangesum(Above(sum(Activity),0,RowNo(Total))) /  Percent

[jdmarlin]

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