Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Finding percentage in pivot table

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.

7 Replies
JonnyPoole
Employee
Employee

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 .

awhitfield
Partner - Champion
Partner - Champion

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

Not applicable
Author

quote missing is typo error

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

JonnyPoole
Employee
Employee

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 .