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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Last 10 week GP%

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

7 Replies
Not applicable

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.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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.

Not applicable

Alright, sorry about that. I guess I do not understant what you are looking for when you said you are trying to combine them.

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Does anyone have any ideas?

swuehl
MVP
MVP

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?