Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

lucarizziero
New Contributor

budget vs sales with left join

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

6 Replies
Not applicable

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

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.

lucarizziero
New Contributor

budget vs sales with left join

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

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.

Not applicable

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.

lucarizziero
New Contributor

budget vs sales with left join

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

Luca

Community Browser