Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Orturcheh
Contributor II
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!

Labels (2)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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;

View solution in original post

7 Replies
tresesco
MVP
MVP

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

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.

tresesco
MVP
MVP

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
Contributor II
Contributor II
Author

"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.
tresesco
MVP
MVP

I ran the script and got output:

Capture.PNG

 

Read about peek()

Orturcheh
Contributor II
Contributor II
Author

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
tresesco
MVP
MVP

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