Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
jdmarlin
New Contributor III

Applying Value from Max Date

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

RegionNamePercentCorrect PercentActivity

Jan 2015

AXYZ Company911100
Feb 2015AXYZ Company9112
Mar 2015AXYZ Company10113
Apr 2015AXYZ Company10111
May 2015AXYZ Company10115
Jun 2015AXYZ Company11115
Jul 2015AXYZ Company11111

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Applying Value from Max Date

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;

View solution in original post

8 Replies
MVP
MVP

Re: Applying Value from Max Date

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;

View solution in original post

Digvijay_Singh
Honored Contributor III

Re: Applying Value from Max Date

Check this -

jdmarlin
New Contributor III

Re: Applying Value from Max Date

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
New Contributor III

Re: Applying Value from Max Date

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
Honored Contributor III

Re: Applying Value from Max Date

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

MVP
MVP

Re: Applying Value from Max Date

maybe

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

MVP
MVP

Re: Applying Value from Max Date

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

jdmarlin
New Contributor III

Re: Applying Value from Max Date

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?

PeriodNameAccount NumberPercentActivity
1/1/2014XYZ Company19100
1/1/2014XYZ Company29100
1/1/2014XYZ Company11950
1/1/2014XYZ Company12950
1/1/2014XYZ Company21925
1/1/2014XYZ Company22925
1/1/2014ABC Company191
1/1/2014ABC Company291
1/1/2014ABC Company1195
1/1/2014ABC Company1295
1/1/2014ABC Company2192
1/1/2014ABC Company2292
2/1/2014XYZ Company191
2/1/2014XYZ Company291
2/1/2014XYZ Company1195
2/1/2014XYZ Company1295
2/1/2014XYZ Company2192
2/1/2014XYZ Company2292
2/1/2014ABC Company191
2/1/2014ABC Company291
2/1/2014ABC Company1195
2/1/2014ABC Company1295
2/1/2014ABC Company2192
2/1/2014ABC Company2292
3/1/2014XYZ Company191
3/1/2014XYZ Company291
3/1/2014XYZ Company1195
3/1/2014XYZ Company1295
3/1/2014XYZ Company2192
3/1/2014XYZ Company2292
3/1/2014ABC Company191
3/1/2014ABC Company291
3/1/2014ABC Company1195
3/1/2014ABC Company1295
3/1/2014ABC Company2192
3/1/2014ABC Company2292
4/1/2014XYZ Company191
4/1/2014XYZ Company291
4/1/2014XYZ Company1195
4/1/2014XYZ Company1295
4/1/2014XYZ Company2192
4/1/2014XYZ Company2292
4/1/2014ABC Company191
4/1/2014ABC Company291
4/1/2014ABC Company1195
4/1/2014ABC Company1295
4/1/2014ABC Company2192
4/1/2014ABC Company2292
5/1/2014XYZ Company191
5/1/2014XYZ Company291
5/1/2014XYZ Company1195
5/1/2014XYZ Company1295
5/1/2014XYZ Company2192
5/1/2014XYZ Company2292
5/1/2014ABC Company191
5/1/2014ABC Company291
5/1/2014ABC Company1195
5/1/2014ABC Company1295
5/1/2014ABC Company2192
5/1/2014ABC Company2292
6/1/2014XYZ Company191
6/1/2014XYZ Company291
6/1/2014XYZ Company1195
6/1/2014XYZ Company1295
6/1/2014XYZ Company2192
6/1/2014XYZ Company2292
6/1/2014ABC Company191
6/1/2014ABC Company291
6/1/2014ABC Company1195
6/1/2014ABC Company1295
6/1/2014ABC Company2192
6/1/2014ABC Company2292
7/1/2014XYZ Company191
7/1/2014XYZ Company291
7/1/2014XYZ Company1195
7/1/2014XYZ Company1295
7/1/2014XYZ Company2192
7/1/2014XYZ Company2292
7/1/2014ABC Company191
7/1/2014ABC Company291
7/1/2014ABC Company1195
7/1/2014ABC Company1295
7/1/2014ABC Company2192
7/1/2014ABC Company2292
8/1/2014XYZ Company191
8/1/2014XYZ Company291
8/1/2014XYZ Company1195
8/1/2014XYZ Company1295
8/1/2014XYZ Company2192
8/1/2014XYZ Company2292
8/1/2014ABC Company191
8/1/2014ABC Company291
8/1/2014ABC Company1195
8/1/2014ABC Company1295
8/1/2014ABC Company2192
8/1/2014ABC Company2292
9/1/2014XYZ Company191
9/1/2014XYZ Company291
9/1/2014XYZ Company1195
9/1/2014XYZ Company1295
9/1/2014XYZ Company2192
9/1/2014XYZ Company2292
9/1/2014ABC Company191
9/1/2014ABC Company291
9/1/2014ABC Company1195
9/1/2014ABC Company1295
9/1/2014ABC Company2192
9/1/2014ABC Company2292
10/1/2014XYZ Company191
10/1/2014XYZ Company291
10/1/2014XYZ Company1195
10/1/2014XYZ Company1295
10/1/2014XYZ Company2192
10/1/2014XYZ Company2292
10/1/2014ABC Company191
10/1/2014ABC Company291
10/1/2014ABC Company1195
10/1/2014ABC Company1295
10/1/2014ABC Company2192
10/1/2014ABC Company2292
11/1/2014XYZ Company191
11/1/2014XYZ Company291
11/1/2014XYZ Company1195
11/1/2014XYZ Company1295
11/1/2014XYZ Company2192
11/1/2014XYZ Company2292
11/1/2014ABC Company191
11/1/2014ABC Company291
11/1/2014ABC Company1195
11/1/2014ABC Company1295
11/1/2014ABC Company2192
11/1/2014ABC Company2292
12/1/2014XYZ Company191
12/1/2014XYZ Company291
12/1/2014XYZ Company1195
12/1/2014XYZ Company1295
12/1/2014XYZ Company2192
12/1/2014XYZ Company2292
12/1/2014ABC Company191
12/1/2014ABC Company291
12/1/2014ABC Company1195
12/1/2014ABC Company1295
12/1/2014ABC Company2192
12/1/2014ABC Company2292
1/1/2015XYZ Company19100
1/1/2015XYZ Company29100
1/1/2015XYZ Company11950
1/1/2015XYZ Company12950
1/1/2015XYZ Company21925
1/1/2015XYZ Company22925
1/1/2015ABC Company191
1/1/2015ABC Company291
1/1/2015ABC Company1195
1/1/2015ABC Company1295
1/1/2015ABC Company2192
1/1/2015ABC Company2292
2/1/2015XYZ Company191
2/1/2015XYZ Company291
2/1/2015XYZ Company1195
2/1/2015XYZ Company1295
2/1/2015XYZ Company2192
2/1/2015XYZ Company2292
2/1/2015ABC Company191
2/1/2015ABC Company291
2/1/2015ABC Company1195
2/1/2015ABC Company1295
2/1/2015ABC Company2192
2/1/2015ABC Company2292
3/1/2015XYZ Company191
3/1/2015XYZ Company291
3/1/2015XYZ Company1195
3/1/2015XYZ Company1295
3/1/2015XYZ Company2192
3/1/2015XYZ Company2292
3/1/2015ABC Company191
3/1/2015ABC Company291
3/1/2015ABC Company1195
3/1/2015ABC Company1295
3/1/2015ABC Company2192
3/1/2015ABC Company2292