topic Re: Stop changing percentage in App Development

Stop changing percentage

leobrand — Thu, 05 Jul 2018 00:14:44 GMT

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?

Re: Stop changing percentage

Lisa_P — Thu, 05 Jul 2018 04:21:58 GMT

What is in your pivot table ?

Re: Stop changing percentage

quddus_mohiuddi — Thu, 05 Jul 2018 05:05:57 GMT

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-scope.htm

Re: Stop changing percentage

ankit777 — Thu, 05 Jul 2018 06:34:22 GMT

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.])

Re: Stop changing percentage

leobrand — Thu, 05 Jul 2018 14:25:02 GMT

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.

Movie	Dec 2016	Nov 2016	Oct 2016	Sep 2016
Action	19.9%	22.5%	20.8%	21.8%
Comedy	3.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

Movie	Dec 2016	Nov 2016	Oct 2016	Sep 2016
Action	87.0%	83.4%	86.5%	83.5%
Comedy	13.0%	16.6%	13.5%	16.5%

Re: Stop changing percentage

leobrand — Fri, 06 Jul 2018 02:14:06 GMT

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?

Re: Stop changing percentage

PrashantSangle — Fri, 06 Jul 2018 03:41:08 GMT

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,