4 Replies Latest reply: Oct 11, 2011 9:58 AM by Sam Sansome RSS

    Counting Order Values

    Sam Sansome

      I know I am missing something simple here but I just can't see what it is....

       

      I am trying to analyse the invoice and order values that we process to identify where we should set our minimum order value.

       

      To do this I have created the variable OrderTotalValue aggr(SUM(SALE),ORD) 

      SALE is the value of the order line and ORD is the order number itself.

       

      This variable gives me positive results elsewhere so I am trusing that it works (my first mistake?)

       

      But if I ask it to split the order values as <=£1000 and >£1000 they do not add up to the total.

       

      For example:

      <=£1000: =SUM(IF(INVOICEDATE>='FromDate' and INVOICEDATE<='ToDate' and $(OrderTotalValue)<=1000,$(TotalOrderValue))) : £4,087,803

      >£1000: =SUM(IF(INVOICEDATE>='FromDate' and INVOICEDATE<='ToDate' and $(OrderTotalValue)>1000,$(TotalOrderValue))) : £1,482,852

      But the total of all sales between FromDate and ToDate is £6,323,919

       

      Using the count function seems to work:

      <=£1000: =COUNT(DISTINCT(IF(INVOICEDATE>='FromDate' and INVOICEDATE<='ToDate' and $(OrderTotalValue)<=1000,ORD)) : 17665

      >£1000: =COUNT(DISTINCT(IF(INVOICEDATE>='FromDate' and INVOICEDATE<='ToDate' and $(OrderTotalValue)>1000,ORD)) : 1555

      Total Number of orders between FromDate and ToDate is 19220

       

      Can you see where I am going wrong?

       

      Any assistance is greatfully received

       

        • Counting Order Values
          Stefan Wühl

          Hi,

           

          in above example, is there a difference between variables TotalOrderValue and OrderTotalValue? You use both in your expression (or is it just a typo in your posting?).

           

          Where do you use these expressions? In a table / chart  with dimensions or as a expresion in a textbox?

           

          I think it would be best if you could upload a small example file, if possible.

           

          Regards,

          Stefan

            • Counting Order Values
              Sam Sansome

              Stefan

               

              Many thanks for your reply.

               

              You are correct.  A slight typo.  They should all be OrderTotalValue (it is correct in my variables and expressions)

               

              I was trying to build a chart/table showing the value of orders within a range and the % of total company sales.  This would help to identify where we are 'wasting' effort on small value orders.

               

              I started with a simple distinction of > or < £1000 to test the theory.

               

              Ultimately I could use distinct textboxes to demonstrate the result.

               

              I'm sorry but I wouldn't know where to start with uploading a sample.  Have I supplied sufficient information for a result?

                • Re: Counting Order Values
                  Stefan Wühl

                  Hi,

                   

                  you could upload a sample in advanced editor (follow the link on upper right of your editor window) or by editing an existing post.

                   

                  I created a small one as example, please check if this comes close to your problem, we could then use mine to discuss your problem, if you want (or upload a corrected one).

                   

                  I tried implementing you variable and compared the partial sums (for < 100 and > 100) with the total sum.

                   

                  In my example, I don't see a difference, so I think my example is not exactely like your setting.

                   

                  Hope this helps,

                  Stefan

                    • Re: Counting Order Values
                      Sam Sansome

                      Stefan -

                       

                      Your advice has inadvertantly helped me.

                       

                      I tried to create a small QV document with a limited amount of the data to supply to you as a sample.

                       

                      This highlighted that there was an area of the load script that was incorrect and was not including all of the ORD numbers,

                       

                      Once I had amended the script the problem has gone away.

                       

                      Many thanks for your persistent assistance.

                       

                      Regards

                       

                      Sam