6 Replies Latest reply: Apr 26, 2012 10:16 AM by Luca Rizziero RSS

    budget vs sales with left join

    Luca Rizziero

      Hi all,

       

      I have 3 fact table:

      Budget

      • year
      • customer
      • budget_amount

      Sales

      • idSale
      • year
      • area
      • customer

      Sales details

      • idSale
      • product
      • sell_amount

       

      I need to compare budget vs sales ignoring any area or product filter.

       

      Thanks

        • budget vs sales with left join

          Try using left keep.

           

          I am assuming you join budget based on year and customer.

           

          so with dimensions Customer and Year you should be able to do sum(budget_amount) vs sum(sell_amount)

          • budget vs sales with left join
            Nicole Smith

            You can tell QlikView to ignore filters in Set Analysis:

             

            sum({<product = , area = >}budget_amount)

             

            This will ignore anything that is selected in product or area.

              • budget vs sales with left join
                Luca Rizziero

                In my example I simplify the situation a lot: I have many fields I should ignore according your kind suggestion.

                It should be better to have a way to limit the possible filtered fields to be considered (for instance, in my example I'd need to consider any possible filter on the year and customer fields only).

                 

                Thanks

                  • budget vs sales with left join
                    Nicole Smith

                    You can ignore all selections but certain ones by using an expression like this:

                     

                    sum( {1 < Field = {$(=getfieldselections ( Field ))} >} budget_amount)

                     

                    where Field is the value you want to be considered.  The "1" makes it ignore everything else.  You can add more Fields into the set analysis if desired.

                      • budget vs sales with left join

                        This will not work if nothing is selected in Field, or everything is selected in field.

                         

                        I am not sure how far back it goes version wise, but try using

                        {1<Field=P()>} in the set. Anything set with =P() will be taken into consideration.

                         

                        sum({1<Customer=P(),Year=P()>}Sales_Amount)

                         

                        This will include any selections in Customer, and and selections in Year.

                         

                        Also note that this is not perfectly showing all customers and years. I believe that it shows all Possible Customers and all Possible Years.

                        If you make an outside selection other than customer, which in turn limits say the customer from 50 down to 10 possible values, this will return all sales for those 10 customers.

                        • budget vs sales with left join
                          Luca Rizziero

                          Thank you all for your help: it was very useful.

                           

                          Luca