Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ba11
Contributor III
Contributor III

Cumulative sum

Hi everyone, in the below table, I want to calculate the value of the total sales target divided by the count of dates.

At the moment it's only returning the correct value at the total level and not the date level. What I want is to get the sales target value for each date so I can then do a cumulative sum as in the second table below

ba11_1-1712057240361.pngba11_3-1712057417647.png

Thanks for all your help.

 

 

 

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
BenjaminT
Partner - Creator
Partner - Creator

For each row you are dividing the Total Sales by one, because at the row level count(distinct(Date)) = 1.

You could try count(total distinct(Date)) instead?

View solution in original post

5 Replies
BenjaminT
Partner - Creator
Partner - Creator

could you tell us what expression you are using in your example?

ba11
Contributor III
Contributor III
Author

Hi,

I'm using 

sum(TARGET) / count(distinct(Date))

BenjaminT
Partner - Creator
Partner - Creator

For each row you are dividing the Total Sales by one, because at the row level count(distinct(Date)) = 1.

You could try count(total distinct(Date)) instead?

ba11
Contributor III
Contributor III
Author

That worked, thank you Benjamin🙂

ba11
Contributor III
Contributor III
Author

Hi again,

Now I'm trying to add the actual sales value but again, it's returning the total value for each day as opposed to the sales amount per day. 

The expression for my actual is:

sum({<Date*={"$(vCalRange)"},[Invoice Type]*={'Invoice'}>}AMOUNT * SELL_RATE)

ba11_0-1712135820630.png