topic Re: Applying Value from Max Date in QlikView

Applying Value from Max Date

jdmarlin — Wed, 12 Aug 2015 18:24:04 GMT

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.

Re: Applying Value from Max Date

MK_QSL — Wed, 12 Aug 2015 19:07:07 GMT

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;

Re: Applying Value from Max Date

Digvijay_Singh — Wed, 12 Aug 2015 19:55:11 GMT

Check this -

Re: Applying Value from Max Date

jdmarlin — Wed, 12 Aug 2015 20:29:05 GMT

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.

Re: Applying Value from Max Date

jdmarlin — Wed, 12 Aug 2015 20:30:28 GMT

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

Re: Applying Value from Max Date

Digvijay_Singh — Wed, 12 Aug 2015 20:53:20 GMT

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

Re: Applying Value from Max Date

maxgro — Wed, 12 Aug 2015 22:14:03 GMT

maybe

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

Re: Applying Value from Max Date

MK_QSL — Thu, 13 Aug 2015 05:32:24 GMT

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

Re: Applying Value from Max Date

jdmarlin — Fri, 14 Aug 2015 15:15:07 GMT

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