4 Replies Latest reply: Oct 23, 2014 2:59 PM by Jonathan Poole RSS

    Expression Help

      Hello:

       

      I am hoping to calculate a Volume Change metric as an expression.  An abbreviate d version of my table structure is as follows:

       

      Geography          Time Period          Target          Volume

      Boston                 QTD                      ONC            25

      Boston                QTD                      Other           27    

      Boston                1 Month                 ONC           10

      Boston                1 Month                 Other           8

      Boston                3 Months               ONC            30

      Boston                3 Months               Other           25

      New York           QTD                       ONC            43



      To achieve a total volume metric, my expression looks something like this:

       

           =sum(Volume)

       

      But now I want to do a Volume Change metric: (Current Time Period Volume - Prior Time Period Volume) / Prior Time Period Volume

       

      How would I go about achieving such an expression with my current table setup??

       

      I have a feeling that I would need to add another column for 'Previous Volume' but I do not really want to do this.

       

      Any/all help is most appreciated.

       

      Thank You!!

        • Re: Expression Help
          Jonathan Poole

          In your abbreviated data the only field that gives any time context is 'time period' but its bucketted into restricive / pre-aggregated values like QTD, 1 Month, 3 Month.

           

          If you can access a more granular source of data that just has Dates in it , you can use many techniques in qlik to show any bucket of dates for period vs period comparison.

           

          Do you have access to the raw values reported by date or date/time ?

          • Re: Expression Help

            Hi,

            I have a straight table,in which we use expressions to show the "Profit" and other details  for each product.

            We calculate the "Profit" on the fly(depending on the selections).In the same table we have a "Range" column,which is calculated on the basis of "Profit". Now, I need another object to select the "Range".

            Product Name(Not Null)Mfg Date(Not Null)ProfitRangeQuantity
            abc1/10/2014700000-1000004
            xyz1/10/2014500000400001-5000005
            asdf1/10/2014150000100001-4000006

            To elaborate,the above table is my staright table.I have some predefined "Range",Like 0-100000,100001-400000 ,400001-500000 .Now I need another object(List Box or anything) of "Range" so that I can select the table depending on the range.

            How to implement this logic?

              • Re: Re: Expression Help
                Jonathan Poole

                Here are 2 approaches to creating a list box to select range.

                 

                Both pre-aggregate the profits to the Product / Date combination granularity. So when you select a range it is going to show you product/date combinations that have that amount of profit.

                 

                The Class function is great for this because its 100% dynamic. However the bucket sizes/ranges are fixed. Below i set to 100000.

                 

                Nested If provides more customized bucket size but the expression is longer .

                 

                One issue is that both methods only bucket for the range that you have in the live data so you will notice missing buckets.

                 

                There is probably a not too difficult way of handling that if you expect gaps in your data / ranges .

                 

                Capture.PNG.png

              • Re: Expression Help
                Steve Taylor

                I'm assuming you have more than those time periods in your system.

                 

                For example, if you have '1 Month' do you also have 'Last Month'?

                 

                If so:

                 

                Volume change = sum({$<[Time Period]={'1 Month'}>}Volume)- sum({$<[Time Period]={'Last Month'}>}Volume)

                 

                Or something along those lines since I can't see much from your post.