topic Re: Problems with P() and filters in QlikView

Problems with P() and filters

bentley32 — Tue, 23 Mar 2021 18:06:38 GMT

Hello everyone,

Let's say i do have 2 hierarchies, city and country

I am trying to create a measure which for each city will give me the avg sales for the whole country

I tried this :

= avg({$1<Country=p()>} Sales) and it worked

But whenever i do filter with a dimension that is not in the hierarchy, per exemple type of sales, it will bring also the sales of other countries since i am asking in my formula all the possible values of each filter ...

Then i tried this avg({$1<Country, Type=p()>} Sales) and it worked again, but when i filter with the country, im getting again a wrong result with all the possible combinations of country and type ...

Do you have a trick to overcome this problem ?

Thanks

Re: Problems with P() and filters

chrismarlow — Tue, 23 Mar 2021 19:06:14 GMT

Hi,

I've not seen P() used like that & $1 means use the previous selection, so confused as to what you mean by working.

From what you describe I would have expected to use something like avg(TOTAL <Country> Sales), meaning discount the Country when taking the average, but it really depends on what you are using this in (you might need an aggr to average an average).

Cheers,

Chris.

Re: Problems with P() and filters

bentley32 — Tue, 23 Mar 2021 19:17:37 GMT

Hi, thanks for your help,

Doing the avg(TOTAL <Country> Sales) i can't have the sales of the country related to the city when i am filtering by a city,

It would just give the result of the city ...

Thanks

Re: Problems with P() and filters

bentley32 — Tue, 23 Mar 2021 21:13:27 GMT

Up ?

Re: Problems with P() and filters

chrismarlow — Wed, 24 Mar 2021 19:15:33 GMT

Hi,

How about;

avg(1)*avg({<City=>}TOTAL <Country> Sales)

Cheers,

Chris.

Re: Problems with P() and filters

bentley32 — Fri, 16 Apr 2021 22:07:24 GMT

Didnt work neither ...

Re: Problems with P() and filters

chrismarlow — Sat, 24 Apr 2021 16:28:51 GMT

Hi,

If you are still looking for a solution you will need to share more, so above suggestion was based off toy application (script below), which gives following behavior;

Now that may or may not be what you want, if it is and it does not work then would need to understand more about your data model to make alternate suggestions.

Cheers,

Chris.

data: Load * Inline [ City, Country, Type, Sales London, UK, 1, 10 Birmingham, UK, 1, 15 London, UK, 2, 20 Birmingham, UK, 2, 30 Paris, France, 1, 40 ];