Bar Chart by % Question (Error)

[FelipeK16]

Hi Guys,

I am here trying to get some help, the Issue is that when selecting a control filter in this case this filter is a column for a product segments, the Bar chart by % I built, is showing the results as a 100%, I mean, when I select for example a product segment called 'x', instead of showing the real % which should be eg. 15% of the total product segments, it shows 100%, I am using a dimension by Year & Month, I attached some screenshots showing the Issue I referred.

One of the problems I had for this Chart was to figure it out on how to build the measure for the %, I finally get a solution which was 

count(distinct{$<termstartdate_t0328vj = {">=$(=Date#('2022-01-01','YYYY-MM-DD'))<=$(=Today())"},
packageexc_t0328vj={'Assistance','Navigation','Both'}>} VIN_t0328vj)

/

count(distinct{$<termstartdate_t0328vj = {">=$(=Date#('2022-01-01','YYYY-MM-DD'))<=$(=Today())"},
packageexc_t0328vj={'Assistance','Navigation','Both'}>} total <termstartdate_t0328vj.autoCalendar.YearMonth> VIN_t0328vj)

This measure was build for the Bar dimension

=if(Match(periodterm_t0328vj,'1-Month','12-Month') and Match(packageexc_t0328vj,'Assistance','Navigation','Both'),periodterm_t0328vj)

And this was for the control filter

=if(Match(periodterm_t0328vj,'1-Month','12-Month') and Match(packageexc_t0328vj,'Assistance','Navigation','Both'),periodterm_t0328vj)

The reason I had to include the conditionals over Bars and control filter was because this was the only way I found to finally have the right distribution on % over the year and Month dimension

The requirement from the client was that He wanted to see this Period Term bars by % distributed from 0% to 100%.

Please feel free to ask any question.

Note: Screenshot (155) shows the Data distributed on % for the segments 1-Month and 12-Month from the column periodterm_t0328vj, Screenshot (156) shows the result when selected one the two segments, 1-Month or 12-Month.

Thank you,

[rubenmarin]

Hi, I'm not sure of what you want at the, it is that when a term period is selcted it keeps the percentage as it were in screenshot 155, so for 2022-feb it keeps the 84%instead of showing 100%?

You can try adding adding a condition to the divisor so it ignores selections on Term Period, like:

count(distinct{$<termstartdate_t0328vj = {">=$(=Date#('2022-01-01','YYYY-MM-DD'))<=$(=Today())"},
packageexc_t0328vj={'Assistance','Navigation','Both'},[TermPeriodField]>} total <termstartdate_t0328vj.autoCalendar.YearMonth> VIN_t0328vj)

Setting a field in set analysis without assigning any value means to ignore selections on that field.

[FelipeK16]

Hi Ruben thank you for your contribution, I did what you suggested but when selecting any segment on the control filter periodterm on the dashboard the chart keeps showing the bar from 0% to 100% as screenshot (156). 

I have added also the periodterm both numerator and denominator but didn't work.

Any other idea? I will appreciate it

This is the measure I have tried

count(distinct{$<termstartdate_t0328vj = {">=$(=Date#('2022-01-01','YYYY-MM-DD'))<=$(=Today())"},
packageexc_t0328vj={'Assistance','Navigation','Both'},periodterm_t0328vj>} VIN_t0328vj)

/

count(distinct{$<termstartdate_t0328vj = {">=$(=Date#('2022-01-01','YYYY-MM-DD'))<=$(=Today())"},
packageexc_t0328vj={'Assistance','Navigation','Both'},periodterm_t0328vj>} total <termstartdate_t0328vj.autoCalendar.YearMonth> VIN_t0328vj)

I interpret this as: When selecting a segment from periodterm control filter, the chart is showing that the total of the segments are 100% for that month, but it should be showing the distribution from the 100% which is 16% eg. as we can see on screenshot (155) for Feb 2022.

[rubenmarin]

Hi, can you upload a sample app with dummy data that represents your problem?

I tried with this data:

LOAD * inline[
value, term,month
3087,1,6
2090,12,6
2398,1,7
1991,12,7
];

And this expression works:

sum(value)/sum(total <month> {<term>} value)

When no term is selected the bars add up to 100% for each month, when term 1 is selected bars are 60% for june and 55% for july, not 100%

[FelipeK16 · Accepted solution]

Hey Ruben, first of all thank you very much for your help, 

I have found the solution and was by making the denominator a static measure with the use of a regular expression, in this case the (1) like this:

(count(distinct{1<packageexc_t0328vj,periodterm_t0328vj,[termstartdate.autoCalendar.Year],
[termstartdate.autoCalendar.Month],isEnrolled_t0328vj={'Yes'},isOnTrial_t0328vj={'No'}>}(VIN_t0328vj)))

Instead of the $, using the 1, this makes the denominator static, so when filtering by any period term, it doesn't affects the measure and now we can see the bars from 0 to 100% when not selecting anything or when selecting both periods, and when selecting one of the segments we can see the real percentage, not as bars from 0 to 100%, in other words that measure was taking the rate for just the two segments eg. the rate for all subscribers out of 1 segment, that inflates the result because we are rating only one segment, not the general number of segments, by making the denominator static, it is taking the rate from the general rate.

I have found this article as reference

https://community.qlik.com/t5/App-Development/Measure-affected-only-by-one-filter/td-p/1690951

[rubenmarin]

Hi, ok, just note that 1 will ignore all selections of the app, so also the years and months or any other selection won't work, but if that works for your needs it's ok.