Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hopkinsc
Valued Contributor II

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
MVP
MVP

Last 10 week GP%

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

7 Replies
Not applicable

Last 10 week GP%

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
Valued Contributor II

Last 10 week GP%

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

Last 10 week GP%

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
Valued Contributor II

Last 10 week GP%

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
Valued Contributor II

Last 10 week GP%

Does anyone have any ideas?

MVP
MVP

Last 10 week GP%

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
Valued Contributor II

Last 10 week GP%

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?

Community Browser