topic Re: Finding percentage in pivot table in QlikView

Finding percentage in pivot table

Fri, 24 Apr 2015 13:59:44 GMT

Hi i have a table and it contains a filed name country.Which contains country names .I want to find how many times specified country names are populated and percentage of the total count of the country name occurrence to the row count with in a specific date. I am using an expression

count({<country={'India',USA','UK','Singapore','Canada'}>}If(Date >= $(vStartDate) AND Date <= $(vEndDate),country)) to find the count.

and using

count({<country={'India',USA','UK','Singapore','Canada'}>}If(Date >= $(vStartDate) AND Date <= $(vEndDate),country)) /count(TOTAL(country))* 100 to find the avg.But i am not getting the correct result.

Re: Finding percentage in pivot table

JonnyPoole — Fri, 24 Apr 2015 14:08:07 GMT

Couple things to try

1) you are missing a single quote before USA ..it says: ,USA'

count({<country={'India',USA','UK','Singapore','Canada'}>}If(Date >= $(vStartDate) AND Date <= $(vEndDate),country))

2) combine date search in set analysis modifier

count( {<country={'India','USA','UK','Singapore','Canada'}, Date={">=$(vStartDate)<=$(vEndDate)"}>} country)

... but i'd suggest copy/pasting the above in the label of the expression to ensure the date search mask is calculating right .

Re: Finding percentage in pivot table

awhitfield — Fri, 24 Apr 2015 14:11:41 GMT

Hi Ryan,

you have a quote missing before USA, is that just a typo?

Ha previous reply arrived whilst writing - agree with the above, PS you don't need the * 100 if you format the output number as 'show in per cent (%)

Andy

Re: Finding percentage in pivot table

Fri, 24 Apr 2015 14:13:15 GMT

quote missing is typo error

Re: Finding percentage in pivot table

Sun, 26 Apr 2015 04:18:52 GMT

hi,sorry the solutions are not working for me..

Re: Finding percentage in pivot table

Sun, 26 Apr 2015 04:27:16 GMT

@jonathan poole when i am using date expression like you mentioned all the values are showing 1

Re: Finding percentage in pivot table

Sun, 26 Apr 2015 04:27:50 GMT

hi thank for the help,but when i am using date expression like you mentioned all the values are showing 1

Re: Finding percentage in pivot table

JonnyPoole — Mon, 27 Apr 2015 12:47:28 GMT

If i were to guess its probably a date format issue with the Date, and making sure the date range for #2 matches your current Date format for the Date field . Please upload your QVW or a sample QVW .