topic Re: How to sum values taking into account filters? in App Development

How to sum values taking into account filters?

Tue, 18 Oct 2016 17:05:55 GMT

Hi everyone,

I'm qlik sense entry level user...and as all new user problems start so quickly. I have a table containing the fields:

- cities

- countries

- clicks

it looks like:

CITY COUNTRY CLICKS

paris France 100

nice France 50

london UK 10

madrid Spain 5

barcelona Spain 2

In my app I use a filter by COUNTRY and a second filter by CITY and I wanto to show the number of clicks as a KPI value object according to filters applied by the app user. For instance, if I select:

Country filter: France

The KPI object should display 150.

I'm currently using the Sum(CLICKS) function in the KPI object and the displayed value doesn't change with the filter selection. it is always displaying 167 (100+50+10+5+2).

How can I make a sum taking into account the current value of the filters?

Thanks in advance for your help,

Carlos

Re: How to sum values taking into account filters?

sunny_talwar — Tue, 18 Oct 2016 17:15:47 GMT

QlikView by default should be able to filter out un-selected values. Two things to check here

1) Is your data model correctly linked?

2) Are you using set analysis to ignore selection.

I think the issue might be the 1st one, but won't be able say much without looking at your app. Would you be able to share you app here?

Re: How to sum values taking into account filters?

Tue, 18 Oct 2016 17:28:47 GMT

Hi Sunny,

Thanks for the reply. Yep, I can share the app here.

Carlos

Re: How to sum values taking into account filters?

sunny_talwar — Tue, 18 Oct 2016 17:35:26 GMT

Just from a quick investigation, I don't see a link between your two tables here:

Did you mean to keep them separate?

Re: How to sum values taking into account filters?

Tue, 18 Oct 2016 17:52:02 GMT

Not really required actually. All the information I need is in the left table in your picture

Re: How to sum values taking into account filters?

sunny_talwar — Tue, 18 Oct 2016 18:24:01 GMT

Where you are making selection and which of the object is not working?

Re: How to sum values taking into account filters?

Tue, 18 Oct 2016 19:30:53 GMT

They are the "affichages" and "clics" KPIs object displaying 2.54k and 946. Do you have any hint to make those indicators changes with the "pays" and "ville" filters?

Thanks,

Carlos

Re: How to sum values taking into account filters?

sunny_talwar — Tue, 18 Oct 2016 20:56:12 GMT

Those two KPI Objects are using the fields from an unrelated (unlinked) table (I pointed this out earlier also).

total clicks and total afffichages is not related to your other table and won't filter with any selection in fields from the other table. If you want it to filter, you would want to form a connection between them. What is one/ or more common field which is common between the two tables where the two tables can connect with each other?

Re: How to sum values taking into account filters?

sunny_talwar — Tue, 18 Oct 2016 21:00:53 GMT

You can use these two expressions and they seem to be working with selections

1) Sum(nombre_affichages)

2) Sum(nombre_clics)

No selection

With selection

Re: How to sum values taking into account filters?

Wed, 19 Oct 2016 09:02:35 GMT

‌interesting! Can you send me back the app with your modifications pls?

I tried the Sum() function with the same fiels you mention but nothing changed...really weird

carlos

Re: How to sum values taking into account filters?

sunny_talwar — Wed, 19 Oct 2016 16:41:41 GMT

If you open this link : Re: How to sum values taking into account filters? you will see that I have already attached the qvf file for you

Re: How to sum values taking into account filters?

Fri, 21 Oct 2016 09:37:47 GMT

Work like a charm, thanks Sunny!!!

Re: How to sum values taking into account filters?

sunny_talwar — Fri, 21 Oct 2016 15:27:40 GMT

Awesome!! Please close this thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny