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: 
Not applicable

How to subtract previous week's sale from this week's sale?

Hi

I have straight table displaying weekly sales starting from January 1, 2008 to date. Now the problem is that my QV app displays it like this

Week          Sales

201148          133 739

201147          128 880

201146          116 031

...... and so on.

This is wrong!

What I should see instead is, for week 48, it should be ValueOf(201148) - ValueOf(201147) i.e. 4859. So the table should look like this:

Week          Sales

201148          4859

201147          12849

201146          8690

...... and so on.

What I have in the expression(expression in chart properties->expression tab, not in script) is Sum([Total Sales])

My questions is, if it is possible to retain the last value of Sum([Total Sales])? Because then I will have the actual value what should be there.

Something like this=>

Sum([Total Sales]) - Previous(Sum([Total Sales]))

Once again, I need to write expression for this in the Expression tab under chart properties for the straight table I am using and Previous function doesn't work here (it only works in the script). Tell me if it's doable or not. I will not answer questions "Why not change in your script?".

Regards,

/Syed

7 Replies
SunilChauhan
Champion
Champion

use code like below

Sum({$<week={$(=Max(week)}Sale)_Sum({$<week={$(=Max(week)-1}Sale)

Sunil Chauhan
Not applicable
Author

Thanks Sunil,

I just replaced "Sale" with my alias to it which is "[Total Sales]" and now there is no record at all. It's NULL!

jjfabian
Partner - Creator III
Partner - Creator III

Hi Syed,

I would try using Set Analysis here instead.

Maybe something like this:


=sum([Total Sales]) - sum({<Week={$(=(Week-1))>} [Total Sales])


Regards

Jakob

Not applicable
Author

Hi Jakob,

Your expression seemed simpler and more logical, but it doesn't work either. Still got NULL when tried your expression.

Do you think there could be another way of doing it?

Thanks anyway!

/Syed

jjfabian
Partner - Creator III
Partner - Creator III

Set Analysis is the way to go, that I am pretty sure of. You might have to figure out a different way of using it though.

Not applicable
Author

Perhaps you are right because this is what I have been trying so far. I have spent 2 full days on it but invain.

That's why I decided to seek help from the community.

/Syed

Not applicable
Author

Hi Syed

If I have understood your post correctly I believe you have data that shows your sales figures as a cumulative balance for each week but wish to see it as a weekly total plotted against the week number.

E.G.

                    

 

Week

 
 

Total Sales

 
 

Weekly Sales

 
 

201148

 
 

133739

 
 

4859

 
 

201147

 
 

128880

 
 

12849

 
 

201146

 
 

116031

 
 

8690

 

One way to achieve this is to use the RangeSum function.

Set up a straight table with Week as your dimension.  Open the properties of the table and in the expression tab add two expressions:

Sum ([Total Sales])

Sum([Total Sales])-RangeSum (above(Sum([Total Sales]),-1,Count(Week)))

The first expression is a straight forward summation of sales values.  The second expression does two things. Firstly, it calculates the total sales for the previous week.  Secondly, it subtracts this value from the total sales for the current week resulting in a weekly balance.  It repeats this process for each week value listed in your dimension and so populates your table with weekly sales values.

For this solution to work correctly the data must be listed as in the table above with most recent week at the top i.e week sorted descending by week number.

Hope that helps.

Kind regards

Steve