Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I am trying to write a chart to show the last 10 weeks GP% but i am struggling to find the correct expression,
This is an expression in a chart showing the last 10 weeks Sales Value. This expression is the one that shows sales 5 weeks from the selected week.
sum({< Cal_Date=,Cal_Week=,Cal_Year=,Cal_WeekCount={">$(=max(Cal_WeekCount)-6) <=$(=max(Cal_WeekCount)-5)"}>} "Sales Qty")
And this expression shows GP%
([Sales Value Ex VAT] - (Sum ([Cost Value]) - Sum ([Platinum Bonus]))) / [Sales Value Ex VAT]
But i am not sure how to combine them...
Can anyone help?
Hi,
you want to have something like
(SALES - COST) / SALES
if you calculated % over a period, you must use the aggregation for each part of the formula:
(sum(SALES) - sum(COST)) / sum(SALES)
you need to apply any set expression to each sum separately.
I noticed that you don't use a sum for [Sales Value Ex VAT], why so? Is it on a different aggregation level? I don't really understand that.
But hope above helps,
Stefan
If both formulas are working on their own, then you can see if this works:
((sum({< Cal_Date=,Cal_Week=,Cal_Year=,Cal_WeekCount={">$(=max(Cal_WeekCount)-6) <=$(=max(Cal_WeekCount)-5)"}>} "Sales Qty")) - (Sum ([Cost Value]) - Sum ([Platinum Bonus]))) / (sum({< Cal_Date=,Cal_Week=,Cal_Year=,Cal_WeekCount={">$(=max(Cal_WeekCount)-6) <=$(=max(Cal_WeekCount)-5)"}>} "Sales Qty"))
I am assuming that [Sales Value Ex VAT] is meant to be the sum of the sales value from the first expression? This just inserts the fomula in the expression.
Hi, thanks for your reply..
No, the first expression is for SALES QTY which is the units sold..
The second expression is for value but excluding VAT.
I gave both expressions as examples. So i need the set analysis from the first expression for it to show previous weeks but i need the second expression to give me the GP%. I don't need SALES QTY from the first expression.
Alright, sorry about that. I guess I do not understant what you are looking for when you said you are trying to combine them.
Sorry, i should have explained myself better. I want to combine the SET ANALYSIS with the 2nd expression.
Any ideas on how to do that?
Does anyone have any ideas?
Hi,
you want to have something like
(SALES - COST) / SALES
if you calculated % over a period, you must use the aggregation for each part of the formula:
(sum(SALES) - sum(COST)) / sum(SALES)
you need to apply any set expression to each sum separately.
I noticed that you don't use a sum for [Sales Value Ex VAT], why so? Is it on a different aggregation level? I don't really understand that.
But hope above helps,
Stefan
Hi again Stefan.
Thanks for your reply...I was driving home from work earlier and thought to myself... "im sure the set analysis needs to be on each sum".. I will try that in the morning.
To answer your question on the sum for [Sales Value Ex VAT], i am not sure as this particular application was written by someone else. I am adding something to it that someone has asked for. All i do know is that it works spot on the way it is... but i will check tomorrow.
Thanks for your help again .
Does anyone have any thoughts on this?