topic Setting an expression to ignore certain selections in QlikView

Setting an expression to ignore certain selections

Thu, 28 Jul 2011 10:36:30 GMT

I'm quite a novice at Qlikview so any help would be greatly appreciated.

I have 2 tables - 1 table which shows Customers, ProductGroups, Average PPK (all sites), Site1 PPK, Site 2 PPK.

My second table shows ProductGroups, average PPK (all sites), Average PPK Site 1, Average PPK site 2.

At the moment when i select a specific range of customers - the 2nd tables average PPK changes as its obviously working out the average PPK of the selected customers.

what i am trying to achive is for my 2nd table to work out the total average PPK for all customers at all times but i still need the table to take into consideration any date, month, product group selections. Therefore in effect ignoring only the customer selections.

Thanks in advance for any help

Kind Regards

Dan

Setting an expression to ignore certain selections

lironbaram — Thu, 28 Jul 2011 10:46:32 GMT

in the expression the calculate the avarge for all cusomer

write something like this

avg({<Cutomer=>}PPK)

in this expression you use set analsys

and the formula will ignore any selectio n in the field customer

hope its helps you

Setting an expression to ignore certain selections

Thu, 28 Jul 2011 10:56:28 GMT

Thanks for the reply - that has worked perfectly for my total average for all sites!!

But for individual sites PPK i currently the below formula

Avg( {$<DivisionCode = {RCF}>} PricePerKilo )

I tried to incorporate your example into this formula but it ignored my 'DivisionCode' = RCF selection and gave me the same value as the average for all sites!!

thanks in advance

Dan

Setting an expression to ignore certain selections

jonathandienst — Thu, 28 Jul 2011 10:58:38 GMT

Dan

Try

Avg({<DivisionCode = {'RCF'}>} PricePerKilo)

Jonathan

Setting an expression to ignore certain selections

lironbaram — Thu, 28 Jul 2011 10:58:53 GMT

hei dan

can you post some sample data

or just the fields of the tables

so i can get a better idea on your problem

Setting an expression to ignore certain selections

Thu, 28 Jul 2011 11:06:03 GMT

i have

PayerDescription = Customer Name

DivisionID = is the seperate factory's (Site A, Site B) etc.

PricePerKilo

Product Group

so my second table has the dimension of 'Product Group'

expresions are

1. Average PPK all sites - avg({<PayerDescription=>}PricePerKilo)

2. Average PPK SiteA - Avg( {<DivisionCode = {RCF}>} PricePerKilo )

3. Average PPK SiteB - Avg( {<DivisionCode = {DMG}>} PricePerKilo )

so what i would like to achive for expresions 2 & 3 is to again just ignore all 'PayerDescription' selections.

Please let me know if you need more information to understand

Kind Regards

Dan

Setting an expression to ignore certain selections

Thu, 28 Jul 2011 11:27:48 GMT

Hi Jonathan

Thanks for the reply,

that works to show me the average for the one site - but i also need to ignore any 'PayerDiscription' selections so that i get an average for the one site for but for ALL 'PayerDiscriptions' for that site even if i only have 5 payers selected

Any ideas Jonathan

Regards

Dan

Setting an expression to ignore certain selections

lironbaram — Thu, 28 Jul 2011 11:34:42 GMT

hei use

Avg({<DivisionCode = {'RCF'},PayerDescription=>} PricePerKilo)

Setting an expression to ignore certain selections

Thu, 28 Jul 2011 11:37:09 GMT

Perfect!!

Thanks for the help liron!!