7 Replies Latest reply: Aug 16, 2011 5:19 PM by Chris Hopkins RSS

    Last 10 week GP%

    Chris Hopkins

      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?

        • 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.

          • Last 10 week GP%
            Stefan Wühl



            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,


              • Last 10 week GP%
                Chris Hopkins

                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?