5 Replies Latest reply: Jul 12, 2016 6:12 PM by Ugonna Okoli RSS

    Help with expression using multiple variables

    Ugonna Okoli

      Hi All,

      Please I need help with some price analysis in QlikSense; I have an InvoiceSales table with product,quantitysold,actualrevenue and product category fields, and a SuggestedRates table with category and monthly rates.

       

      InvoiceSales:
      LOAD * Inline [
      Product, Category, QuantitySold, ActualRevenue
      Card,  Paper,   6,    3
      Tissue, Paper,   23,   15
      Poster, Paper,   14,   7
      Spoon, Plastic, 9,   4
      Cup,  Plastic,  18,   8
      ];


      SuggestedRates:
      LOAD * Inline [
      Category, Jan-16, Feb-16, Mar-16
      Plastic,  $0.655, $0.635, $0.685
      Paper,    $0.620, $0.620, $0.638
      ];


      My goal is to calculate the 'Estimated Revenue' and 'Revenue Variance' based on the suggested rates for the month selected(see attached sample qvf). So if Jan-16 is selected, 'Estimated Revenue' = (QuantitySold*$0.620) for Paper products, (QuantitySold*0.655) for Plastic products.

       

      How can I achieve this? Is it possible to declare a variable for each month with multiple rates?

      I appreciate the help!

        • Re: Help with expression using multiple variables
          Greg Donnells

          You may want to consider re-organizing your data to align it more dynamically with any chosen dimensions. Please consider:

           

          SuggestedRates:

          LOAD * Inline [

          Category, RefDate, CostRate

          Plastic,  Jan-16, $0.655

          Plastic,  Feb-16, $0.635

          Plastic,  Mar-16, $0.685

          Paper,   Jan-16, $0.620

          Paper,   Feb-16, $0.620

          Paper,  Mar-16, $0.638

          ];

           

          This will expose the data by date and category and will help avoid some challenging set logic to select a data rate...

           

          Hope this helps...

          • Re: Help with expression using multiple variables
            Clever Anjos

            It would be much easier if you crosstable your model

             

            InvoiceSales:

            LOAD * Inline [

            Product, Category, QuantitySold, ActualRevenue

            Card,  Paper,   6,    3

            Tissue, Paper,   23,   15

            Poster, Paper,   14,   7

            Spoon, Plastic, 9,   4

            Cup,  Plastic,  18,   8

            ];

             

             

            SuggestedRates:

            CrossTable(Month,Value)

            LOAD * Inline [

            Category, Jan16, Feb16, Mar16

            Plastic,  0.655, 0.635, 0.685

            Paper,    0.620, 0.620, 0.638

            ];

              • Re: Help with expression using multiple variables
                Ugonna Okoli

                Thanks Clever/Greg! The actual dataset is a lot larger than the sample and it's updated monthly with new rates so I suppose the CrossTable function would work for organizing the data.

                 

                That said, after the data reorg how do I go about calculating the 'Estimated Revenue' and 'Revenue Variance'?

                  • Re: Help with expression using multiple variables
                    Greg Donnells

                    With the data exposed as a column, that column can now be used as a dimension, so you could create a chart object with the dimensions of 'RefDate', 'Product', and 'Category' (order as you see fit). Set your expressions to be:

                     

                    'Estimated Revenue' =  sum(QuantitySold * CostRate)

                    'Actual Revenue' = sum(AcutualRevenue)

                    and 'Variance' = [Actual Revenue] / [Estimated Revenue] - 1 // view this as a percent with 2 fixed digits

                     

                    This should lay out a flexible control and have QlikSense do the heavy lifting for you. You could even make a drill-down group (Category, Product, RefDate) for an active pivot table, etc.

                     

                    Note: (We use QlikView, not QlikSense [yet] so my answers reflect a QlikView Document way of doing this analysis)

                     

                    Hope this helps.