Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Orturcheh
New Contributor II

Merging two values for different months

Hello dear community, 

I have a table of data with columns like monthNumber, Sales, Team, etc..

What I need is to create a column, which will show:

For monthNumber 1 - Sum of Sales for monthNumber 1

For monthNumber 2 - Sum of Sales for monthNumber 1+2

For monthNumber 3 - Sum of Sales for monthNumber 1+2+3

and so on. 

I need to keep the monthNumber column intact, as the data will later be displayed on a bar chart.

I tried to create a new column [Sales Modified], but didn't really succeed.

Thank you in advance for your help!

Tags (2)
Labels (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Merging two values for different months

Try like:

table1:
Load * Inline [
monthNumber,Team,Sales
1,AAA,10
1,BBB,10
1,CCC,10
2,AAA,15
2,BBB,15
2,CCC,15
3,AAA,10
3,BBB,10
3,CCC,10

];
NoConcatenate
table2:
Load
	*,
	If(peek(Team)=Team, RangeSum(peek(Accumulated),Sales), Sales) as Accumulated
Resident table1 order by Team,monthNumber, Sales;	

Drop Table table1;
7 Replies
MVP
MVP

Re: Merging two values for different months

Do you need this in script or in UI? Could you share a small data sample?
Highlighted
Orturcheh
New Contributor II

Re: Merging two values for different months

Thank you for your response .
As far as I understand, it would be best for me to have it in the script, as this "accumulated" sales is then displayed on a bar chart sorted by month.

Sample attached.

MVP
MVP

Re: Merging two values for different months

Try like:

table1:
Load * Inline [
monthNumber,Team,Sales
1,AAA,10
1,BBB,10
1,CCC,10
2,AAA,15
2,BBB,15
2,CCC,15
3,AAA,10
3,BBB,10
3,CCC,10

];
NoConcatenate
table2:
Load
	*,
	If(peek(Team)=Team, RangeSum(peek(Accumulated),Sales), Sales) as Accumulated
Resident table1 order by Team,monthNumber, Sales;	

Drop Table table1;
Orturcheh
New Contributor II

Re: Merging two values for different months

"Accumulated" field not found .

Also, could you elaborate on the "peek formula"?

The reason Im asking is because in the actual data there are also so called "tasks", and each team may or may not be working on same/different tasks.
MVP
MVP

Re: Merging two values for different months

I ran the script and got output:

Capture.PNG

 

Read about peek()

Orturcheh
New Contributor II

Re: Merging two values for different months

But is it normal that in this formula you do a RangeSum of "Accumulated" before even creating the column "Accumulated"?

If(peek(Team)=Team, RangeSum(peek(Accumulated),Sales), Sales) as Accumulated
MVP
MVP

Re: Merging two values for different months

That's true only for the first record in the load sequence. And peek() works silently in such cases without throwing error and halting..Smiley Happy