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

Creating new column with merged values

Hello dear community, 

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

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

For Month 01.2019  - Sum of Sales for monthNumber 1

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

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

and so on. 

I tried to create a new column using peek function, but it gave me strange figures.

This is a more complicated continuation of this question.

Thank you in advance for your help!

Sample data below.

 

sample.PNG

11 Replies
Channa
Specialist III
Specialist III

IF(Previous(month)=Month,RangeSum(Peek(Sales),Sales),Sales) AS rangesales;

 

add to script

Channa
felipessj
Contributor III
Contributor III

hi, try to use flags
Example,
the January data is 1, february data is 2...
then in the expression we can use an if and analysis set.
If Month = January
sum({<Flag = {1}>}total)
if month = february
sum({<Flag={"<=2"}>}total)
Orturcheh
Contributor II
Contributor II
Author

Hi Felipessj,

I dont think set analysis will work for me, as these figures are ten shown on a bar chart and sorted by month
Orturcheh
Contributor II
Contributor II
Author

Channa,

I have a feeling the script is adding values for different teams, which gives strange results
Channa
Specialist III
Specialist III

can you share data in xls

Channa
Orturcheh
Contributor II
Contributor II
Author

I've provided a sample in the top of the page
Channa
Specialist III
Specialist III

try this in your table

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())), Area, Task,Month)

keep ur dimension in bold 

 

Channa
Orturcheh
Contributor II
Contributor II
Author

Still mixes values for different tasks/teams
Channa
Specialist III
Specialist III

try to add sub query in your dataload 


load TASKNUMBER,if(Peek(Month)=Month,rangeSum(peek(Sales),Sales),Sales) as Rangesames;

LOAD
Area,
"Task Number" as TASK,
Division,

Team,
date("Month",'MM.YYYY') as Month,
"Task Number"& Team&Division&Area as TASKNUMBER,
Sales
FROM [lib://TEST/RangeSum2.xlsx]
(ooxml, embedded labels, table is Sheet2);

Channa