topic Re: Count problems in QlikView

Count problems

Thu, 23 Oct 2014 09:29:07 GMT

Hello everybody!

Need you help.

I have table KPI, where I made some Fact and Plan calculations and also wrote simple if expression

if (Fact>=Plan, ‘yes’, ’no’) .

I got ‘yes’ and ‘no’ KPI-es of every id with the fixations at a certain time. That works correct.

The problem is in counting ID-s with ‘yes’ and ‘no’ in chart. How to count in chart diagram values from straight table? I used variables, but this approach also work incorrect.

The result chart should look like this:

Id	Plan	Fact	State
1	1	1	yes
2	1	0	no

If there are any ideas how to do that?

Look forward to you reply

Thanks

Re: Count problems

MK_QSL — Thu, 23 Oct 2014 09:43:44 GMT

Use below three expressions

=SUM(Aggr(IF(sum({<Times={">$(vTimeFrom) <$(vTimeTo)"}>}Fact_fiks)>=sum(Plan_fiks_day),1,0),id))

=COUNT(DISTINCT id)

=SUM(Aggr(IF(sum({<Times={">$(vTimeFrom) <$(vTimeTo)"}>}Fact_fiks)<sum(Plan_fiks_day),1,0),id))

Re: Count problems

Anonymous — Thu, 23 Oct 2014 09:47:47 GMT

1.Define the field State here as the first expr and rename it as State_Calc

2.No of distinct Id:

the Expr is : Count(Distinct id)

3.No of Distinc id with state No:

count({<State_Calc={'No'}>}Distinct id)

4.No of Distinc id with state Yes:

count({<State_Calc={'yes'}>}Distinct id)

Hope this helps u

Re: Count problems

Fri, 24 Oct 2014 08:38:09 GMT

Thank you, it works good! But what if not use binary(1,0) values in sum expression, for example instead of two States "yes" and "no", if I have other several states? In this case, how to count id-s whith other states?

Re: Count problems

Mon, 27 Oct 2014 07:27:56 GMT

Thanks for your reply, it seems that such approach work as mine, not correctly... the fact is that States are dynamically changed according to time ranges. Counts work correctly in stright table, but in chart they dont't

Re: Count problems

MK_QSL — Mon, 27 Oct 2014 10:05:47 GMT

Provide example please...!

Re: Re: Count problems

Mon, 27 Oct 2014 12:58:18 GMT

For example, i created 3 kpi states: ok, shortage, exceeded

According to the KPI table , there are should be 2 id with state 'shortage', 1 id with 'ok' and no one id with 'exceeded' states in Diagramm.

Also qvd file is attached.