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: 
leobrand
Contributor III
Contributor III

Stop changing percentage

I have this measure in a pivot table and when the filter pane is selected for an individual month I get the correct percentage, however once I have more than one month selected in the filter pane the pivot table percentage decreases.

Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total{$<[Movie]=>}Distinct[TicketNo.])

Can you help me modify this measure so that whether an individual month or multiple months are selected in the filter pane the pivot table percentage stays the same?

1 Solution

Accepted Solutions
PrashantSangle

can you share screen shot of your output??

before selection and after selection.

take both expression i.e. numerator and denomenator in 2 KPI object check what was value of it before and after selection.

Not sure it will help you or not but you can try below expression.

Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total<Month> {$<[Movie]=>}Distinct[TicketNo.])


Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

6 Replies
Lisa_P
Employee
Employee

What is in your pivot table ?

quddus_mohiuddi
Partner - Contributor III
Partner - Contributor III

Hi Leo,

I think its happening because you are using Total and $ in set analysis in your divider expression. Try using {1} instead of $ will get your desired result. Read this link for help.

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/define-aggregation-...

ankit777
Specialist
Specialist

When multiple months are selected , which month data you want to show in pivot?

You can put that month in set analysis

for instance, if you want to show it for max month, use like this:

Count({$<[Movie]=,MONTH = {$(=max(MONTH))}>}Distinct[TicketNo.])/Count(Total{$<[Movie]=>}Distinct[TicketNo.])

leobrand
Contributor III
Contributor III
Author

I want the percentages to remain unchanged regardless of the month(s) selected in the filter pane


If Dec, Nov, Oct, or Sep  is selected in the filter pane individually they display the percentages in the pivot table below.


Movie

Dec

2016

Action

87.0%

Comedy

13.0%


Movie

Nov

2016

Action

83.4%

Comedy

16.6%


Movie

Oct

2016

Action

86.5%

Comedy

13.5%

Movie

Sep

2016

Action

83.5%

Comedy

16.5%

However if the filter pane does not have a month selected the percentages above will change to the percentages below.

      

MovieDec 2016Nov 2016Oct 2016Sep 2016
Action19.9%22.5%20.8%21.8%
Comedy3.0%4.5%3.2%4.3%

Regardless of the month or months selected in the filter pane I want the percentages to remain unchanged like in the table below

MovieDec 2016Nov 2016Oct 2016Sep 2016
Action87.0%83.4%86.5%83.5%
Comedy13.0%16.6%13.5%16.5%
leobrand
Contributor III
Contributor III
Author

If this syntax...


          aggr(nodistinct count([Fieldname?]),[MonthYear])


can be successfully incorporated into the denominator in the expression below...


          Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total{$<[Movie]=>}Distinct[TicketNo.])


I think percentages will remain unchanged regardless of the month(s) selected in the filter pane.  Any thoughts?

PrashantSangle

can you share screen shot of your output??

before selection and after selection.

take both expression i.e. numerator and denomenator in 2 KPI object check what was value of it before and after selection.

Not sure it will help you or not but you can try below expression.

Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total<Month> {$<[Movie]=>}Distinct[TicketNo.])


Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂