budget vs sales with left join

[lucarizziero]

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

[#2]

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)

[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.

[lucarizziero]

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

[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.

[#6]

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]

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

Luca