Hi,
I need to calculate a percentage (using total) as a measure in a bar chart (see screenshots) taking into account the selection I make with Month Year only reflect start dates.
Month Year consists of two type of dates: start dates and due dates (values in field %DateOrigin). We wish that for certain metrics we can filter on start dates or due dates with the same filter Month Year. This is the expression used:
=Count({<%DateOrigin= {'Start Date'}>} [ID]) / Count({<%DateOrigin= {'Start Date'}>} total <Objective> [ID])
It works perfect, but for certain selections (combinations of Month Year and (Region or other field)) the denominator (Count({<%DateOrigin= {'Start Date'}>} total <Objective> [ID]) ) gives the same amounts as in the counter (Count({<%DateOrigin= {'Start Date'}>} [ID])). Which is wrong considering the data from the selection.
Only when I additionally select the value Start Date in the filter %DateOrigin, the correct percentage is given. I do not understand why I need to make an extra selection since this is already available in the expression (in both the denominator and counter {<%DateOrigin= {'Start Date'}>}) How can I solve this? I currently added the filter %DateOrigin as a workaround, but this is not ideal for business.
I have also tried to use the 1 in set analysis, but then the percentages are not what I want.
=Count({1<%DateOrigin= {'Start Date'}>} [ID]) / Count({1<%DateOrigin= {'Start Date'}>} total <Objective> [ID])
I could supply a dataset and the qvf but it would take time since I would need to randomize my data first and also change some fieldnames.
I am using Qlik Sense Feb 2019
