topic Re: Count of a column when associated values on another column are on same status in QlikView

Count of a column when associated values on another column are on same status

Prabhu — Sat, 16 Nov 2024 20:12:32 GMT

Hi,

I have three columns: Objective, Results, Status

I want to calculate the count of Objectives only when all the associated "Results" have status "Completed"

Attached the screenshot.

m_woolf — Fri, 23 Aug 2019 13:43:01 GMT

=count({<Results = {'Completed'}>}Objective)

Prabhu — Fri, 23 Aug 2019 14:09:45 GMT

Hi, I want to count the objective only when all it's results has
"completed" status.

m_woolf — Fri, 23 Aug 2019 14:56:05 GMT

Did you try what I suggested?

Prabhu — Fri, 23 Aug 2019 15:15:44 GMT

Yes, it is counting the objective even if it has 1 result as Completed.
Objective should be counted only when all of it's results are Completed

marcus_sommer — Mon, 26 Aug 2019 09:49:30 GMT

Try it with:

sum(aggr(-(count({<Results = {'Completed'}>}Objective)=count(Objective)),Objective))

- Marcus

Prabhu — Mon, 26 Aug 2019 10:05:12 GMT

Thanks Marcus, but your calculation is giving one extra count.

The following calculation gives exact count:

=COUNT(DISTINCT AGGR(if(count( [Product Key Result])=count({<Status={'COMPLETE'}>}Status), ONLY( [Product Objective])), [Product Objective]))

Anil_Babu_Samineni — Mon, 26 Aug 2019 12:16:51 GMT

@Prabhu You got the answer? If so, please mark yourself as correct answer. If not, Please free to ask.