topic Re: Proportion via expression using variables in QlikView

Proportion via expression using variables

mattphillip — Sat, 16 Nov 2024 20:57:08 GMT

Hoping someone might be able to advise where I'm going wrong here. I've previously used expressions including variables to calculate proportions (see below). However, I now need to take it one step further by adding the stipulation of particular field values. For example, I have a a field called 'Response' which contains the values, reject, insurance, decline and accept. I'm trying to produce a proportion in using an expression based on the below purely for those who have the value of accept in the 'Response' field. I've tried a few different ideas to add the field value in but none work ultimately. I'm using the user populated vDenominator variable, which captures the field/final field (if multiple) displayed in a custom pivot table.

Existing expression (which is currently used to calculate the conversion to accepts from the total responses. What I'd like to do is modify the denominator to also specify the field value of accept within the 'Response' field thus allowing for a proportion of accepts across the displayed fields, in this case school and gender (where each school's proportion of accepts adds up to 100%.:

=count({$<[Response]={'Accept'}>} DISTINCT [Applicant ID])/count(TOTAL <[$(vDenominator)]> [Applicant ID])

Does anyone have an idea if this is possible?

Any help would be most appreciated.

Thanks,

Matt

Re: Proportion via expression using variables

dplr-rn — Thu, 02 May 2019 13:21:57 GMT

It is possible
quick question why are you using [] in your expression around the variable?
below works
Sum(Total<$(vSetTotalVariable)> LineSalesAmount)
like Sum(Total<Year> LineSalesAmount)

Re: Proportion via expression using variables

mattphillip — Thu, 02 May 2019 14:47:03 GMT

Thanks for the reply. Actually, the inclusion of the [ ] goes back to a much earlier query I had about something unrelated and the solution was to include those and I've just kept them in. But I'll try taking them out to see if it still works.

In regards to my main query, are you able to share how you would approach it if it is possible?

Many thanks in advance.

Matt