topic Re: Make a KPI with values in QlikView

Make a KPI with values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hey there,

I got a problem on making a KPI. I want to show a KPI which shows a count how many distinct BBDs (Best Before Date) (see table below) are higher than 1. So in the case from the table below it should display 6, because the count higher than 1 is 6.

I tried to make a count(if(count( formula but it al did not give me the right KPI. I run into the problem that I can't use the BBD in the formula, because this is a distinct count value I am making from more BBDs. So $$Prinartsubnr is a dimension and BBD a distinct count from all BBDs, making it a value and which makes it unusable for my KPI (in my opinion)

I have tried to make some sort of formula in my load too, but I couldn't.

$$Prinartsubnr	BBD
6000\|4031289\|1-127	6
6000\|4055368\|1-5600814694	5
6000\|4045796\|1-360428	3
6000\|4055368\|1-18000479	3
6090\|0340029968\|1-180603	2
6090\|0340034411\|1-823B92VT14	2
6000\|20995\|1-8003690658	1
6000\|20995\|1-8003706199	1
6000\|22714\|1-L8163	1
6000\|22714\|1-L8166	1

I am still learning a lot in Qlik and hope you can help me out or send me in the right direction. At the moment I am really stuck on this one.

Thanks in advance,

Paul.

Re: Make a KPI with values

pooja_prabhu_n — Thu, 05 Jul 2018 06:24:04 GMT

Hi,

You can restrict in dimension limits to show BBDs which are higher than 1.

Re: Make a KPI with values

thevingo — Thu, 05 Jul 2018 07:09:28 GMT

Sorry, I cant understand your question properly

Do you want to show it in Table or textbox

Do you want to show max value then use Aggr(Max(..

Re: Make a KPI with values

Anonymous — Thu, 05 Jul 2018 08:25:11 GMT

Hi Pooja,

Thanks for your tip, it helped me out in a way and gave me new insights, but it is not what I am looking for.

See the image below, I count(distinct BBD) combined with $$Prinartsubnr (THTCheck1 in this image) and then I want to count the amount of lines. In this case 100 lines are open with distinct BBD > 1 (as shown in totalen=totals in english)(I could not have done that without your help, it actually gave me the right amount I was looking for, but not on the right spot).

Now I want to make a KPI showing 100, but I can't make the formula right to display this number as it does not allow me to add a dimension and measure together in this KPI. When I just do count(Distinct BBD) is gives me a higher number than it should, as it does not combined with $$Prinartsubnr. a formula like count(if(count does not allow me to combine $$Prinartsubnr and BBD to come to a total of 100.

Bottomline: I want a KPI displaying 100 and when lines get removed or added I want this KPI to react with in- or decrease.

Re: Make a KPI with values

Anonymous — Thu, 05 Jul 2018 09:11:59 GMT

Hi Govind,

A KPI or a variable textbox would also work.

See my reaction to Pooja Nagaraj, maybe that explains better what I want is a result..

Re: Make a KPI with values

sasikanth — Thu, 05 Jul 2018 09:39:28 GMT

HI,

Could you please tell us the formula to calculates BBDs ??

Re: Make a KPI with values

Anonymous — Thu, 05 Jul 2018 09:44:58 GMT

Best Before Date (BBD) is a dimension and I use count(distinct [BBD]) to come to the results in my table.

So for every $$Prinartsubnr it displays the amount of different BBD's, which makes it an value. Thats is why I have a hard time finishing this one I think, because BBD becomes a value instead of a dimension.

Re: Make a KPI with values

sasikanth — Fri, 06 Jul 2018 05:50:04 GMT

Try this,

=Sum(Aggr(Count( Distinct BBD ),$$Prinartsubnr ) )

Re: Make a KPI with values

Anonymous — Fri, 06 Jul 2018 06:47:51 GMT

Hi Sasi,

I tried this, but it returns 4,21K as value. This is way too much as it should only be like 100 as shown in previous images and descriptions.

Think I will have to use a set analysis to come to the right result, but so far I couldn´t figure one out.

The hardest part is combining the distinct BBD when it is higher than 1 with the dimension $$prinartsubnr.

Re: Make a KPI with values

sasikanth — Fri, 06 Jul 2018 07:18:31 GMT

Could you please share your application

Re: Make a KPI with values

Anonymous — Fri, 06 Jul 2018 07:41:45 GMT

How can I extract that from qliksense?

Re: Make a KPI with values

Anonymous — Fri, 06 Jul 2018 10:05:51 GMT

From where I am working I can't extract unfortunately.

Re: Make a KPI with values

Anonymous — Mon, 09 Jul 2018 05:15:12 GMT

Thanks all for the reactions, but don't have the solution yet. Anyone with a solution?

Re: Make a KPI with values

sasikanth — Tue, 10 Jul 2018 10:00:19 GMT

Try this,

=Sum(Aggr(Count({<$$Prinartsubnr ={"=count(Distinct BBD)>1" }>} Distinct BBD ),$$Prinartsubnr ) )

Re: Make a KPI with values

Anonymous — Tue, 10 Jul 2018 12:31:25 GMT

Hi Sasi,

You came really close to the right answer! I changed sum( to count( and that gave me the right result! Thanks a lot!!!!!!!!!

Right formula:

=count(Aggr(Count({<$$Prinartsubnr ={"=count(Distinct BBD)>1" }>} Distinct BBD ),$$Prinartsubnr ) )

Thanks again! Everyone thanks again for help! This got me really excited!

$$Prinartsubnr	BBD
6000\|4031289\|1-127	6
6000\|4055368\|1-5600814694	5
6000\|4045796\|1-360428	3
6000\|4055368\|1-18000479	3
6090\|0340029968\|1-180603	2
6090\|0340034411\|1-823B92VT14	2
6000\|20995\|1-8003690658	1
6000\|20995\|1-8003706199	1
6000\|22714\|1-L8163	1
6000\|22714\|1-L8166	1