18 Replies Latest reply: Mar 13, 2017 1:07 PM by Silvia Ganhao

# Count the total records of one of the fields of the current selection in Qlik Sense

Good day,

I was wondering if there is any Set Analisys statement or an example to apply it to my case.

I want to make an indicator of the Total Sold Product (PV) by Region (REG) of the current selection among the total of Sold Products of the Region to which it belongs.

The formula should look something like this:

Indicator = (PV of the current selection) / (PV of the total of the Region to which it belongs)

* PV = Products Sold

I would greatly appreciate your help, as I have no idea how to do it.

regards

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Luis,

See if this helps

Regards

Sílvia

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

What is the function for the Camp [Sum for Total Region]?

Regards

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Silvia,

Perhaps I didn't write my question correctly.

What I want is, when I select an item from one camp, show from this selection a Subtotal for a camp selected.

If I select in the table "A" in the camp "Product" In "Indicator" show the Total for the Region: "100" and after that show an indicator in percent equal to = Product / Total Region

I hope, you can understand my case.

Thank you very much Silvia

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Try inserting this measure into a table with dimension of REG

=aggr(sum({\$}PV)/sum(PV) , REG)

Regards

Andy

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Andy,

I working with the Silvia's example. But I can't get it...

I think you function is the result what I want to get.

but the first part (To get the Total per Region) I can't...

Do you know how can I solve it?

Regards

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi,

try this...

Continuing with Silvia's response.

Best regards,

German

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi German,

Sorry, but I didn't understand you answer...

Regrets

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Luís,

Gving what you request, I think Germán's answer is the response you want,

The first indicator, shows th total for the region associated with the product you choose, I than the total of that product in that region.

Regards,

Sílvia

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Silvia,

That's what I need.

But, How Can I Calculate the "SUM FOR TOTAL REGION" Column.

That's my doubt

Regards.

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Luis,

Not sure I understand, but the expression I used to calculate "SUM FOR TOTAL REGION" Column is:

Sum(Total <Region> Value)

It says that you want the total, for Region.

Regards,

Sílvia

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

I Have a Question,

I put the function as how you placed...

But, If I filter product for e.g. Product "A", my "SUM FOR TOTAL REGION" changes, and only shows the total for the current selection.

Here's some images to support my explain:

1) The Filter

2) Sum for Total Region changes when I'm selecting a product in the table.

I'd like to get a Filter to show a product filtered and to get the Total by Region

Product "A"     |     South     |     10     |     100

I hope you can understand me

Regards, and thank you for your time

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Luis,

Try this:

Sum(Total <Region>  {<Product=>}Value)

Sílvia

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Silvia

I put the function:

Sum(Total <Region>  {<Product=>}Value) It works already.

I made a KPI to don't show fully the table, And I put the next function to get a percentage from the Total per Region (using your function): sum([Sum of Product]) / Sum(Total <Region> {<Product=>}[Sum of Product])

But It doesn't show only the percentage for the Region from a Product selected

I expected to show as result:

sum([Sum of Product]) / Sum(Total <Region> {<Product=>}[Sum of Product])

10 / 100 = 10 %

Once again... Could you help me in my doubt?

Regards

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Luis,

Try this:

Sum(Value) / Sum(Total <Region> {<Region= p(Region),Product=>} Value)

Regards,

Sílvia

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Here is an example with a pivot table.
In the pivot table there is a field that calculates the total percentage per product in the region and the total percentage of the selection if any is made. Or do You require KPI objects per region?

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Lucke,

My doubt is How can I calculate the "SUM FOR TOTAL REGION" column, in the first table. I'd like to know what's the correct function to calculate this unique sum by region.

I hope you can understand me.

Regards

Luis

• ###### Re: Count the total records of one of the fields of the current selection in Qlik Sense

Hi Silvia,

That's the correct answer for my case.

It works perfectly...

Thank you very much, It's good to receive part of your knowledge in this kind of cases.

Regards

Luis