Make a KPI with values

[Anonymous]

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.

[pooja_prabhu_n]

Hi,

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

[thevingo]

Hi

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(..

[Anonymous]

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.

[Anonymous]

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..

[sasikanth]

HI,

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

[Anonymous]

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.

[sasikanth]

Try this,

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

[Anonymous]

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.

[sasikanth]

Could you please share your application