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

[#1]

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

[SunilChauhan]

use code like below

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

[#3]

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]

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

[#5]

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]

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.

[#7]

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

[#8]

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