topic Re: Count values of a Pivot table in QlikView

Count values of a Pivot table

Wed, 01 Feb 2017 00:03:51 GMT

Hi there,

I'd like some assistance with a pivot table, I've prepare the Doc attached as reference and I'll try to explain as much as I can. So, I have a column on a pivot table that displays either Passed or Failed depending on the result of another column on the same table, it works fine but I want to change the 'Total' line for that column to display "Failed" if there are 1 or more Failed status on the column. Then I also need to count how many Failed status appear on that column.

Screenshot below as reference:

Re: Count values of a Pivot table

sunny_talwar — Wed, 01 Feb 2017 10:28:39 GMT

See if the attached is what you wanted?

Re: Count values of a Pivot table

Wed, 01 Feb 2017 12:28:03 GMT

Thanks Sunny,

It works fine and provides the desired result. Appreciate your help!!

Any chances for a little explanation? Specially the "MinString(Aggr"

Re: Count values of a Pivot table

sunny_talwar — Wed, 01 Feb 2017 12:50:15 GMT

MinString behaves like Min() function but it works on a text field. In your case there were two options: Passed and Failed and since F is smaller than P, MinString looked at all your rows and picked the lowest text to display for the total row.

Does this make sense?

Re: Count values of a Pivot table

Wed, 01 Feb 2017 13:23:40 GMT

Makes sense, totally.

Definitely you attacked the problem with a different approach and found a simple solution. Thank you very much for your assistance, it helped me in more than 1 way.

Re: Count values of a Pivot table

meliball — Sun, 23 Feb 2020 18:28:02 GMT

Hi Sunny

I don´t understand your answer, what is it all this?

If(IF((SUM(C) / SUM(A))=0,'-',(SUM(C) / SUM(A)))>=0.95 or IsText(IF((SUM(C) / SUM(A))=0,'-',(SUM(C) / SUM(A)))), 1)

I have a similar problem. I need to count the number of companies, per country, when the result of the test expression is"wrong"

I am doing

Aggr(Count(distinct([EO Name])), [EO Country])

and it works perfectly to count the number of companies (EO Name) per country

But when I want to count the number of companies that failed the test per country, I do:

Aggr(Count(If([Pass test]='wrong', 1)), [EO Name], [EO Country])

And it just retrieve zeros, which is incorrect

what is wrong with my code?

thanks for your help