topic Re: Formula with missing data in QlikView

Formula with missing data

pascos88 — Wed, 24 Feb 2016 15:13:29 GMT

Hello everyone,

I have this problem.

In my chart I calculated a theoretical value like this :

=sum([KWh Real]/kWp)/Count(DISTINCT Company).

Whan I have all data, my theoretical value is good, the problem is whan the data missing.

The problem is that I always take the count(Company), but whan the data missing I should take just the number of the company that have the datas.

Someone can help me?

Regards

Pasquale

alexandros17 — Wed, 24 Feb 2016 15:15:49 GMT

Try something like:

If(isnull([KWh Real]),

Count(DISTINCT Company).,

sum([KWh Real]/kWp)/Count(DISTINCT Company)

)

Let me know

sunny_talwar — Wed, 24 Feb 2016 15:19:00 GMT

May be this:

=Sum([KWh Real]/kWp)/Count(DISTINCT {<[KWh Real] = {"=Len(Trim([KWh Real])) > 0"}>} Company)

=Sum([KWh Real]/kWp)/Count(DISTINCT {<[KWh Real] = {"=Len(Trim([KWh Real])) > 0"}, kWp = {"=Len(Trim(kWp)) > 0"}>} Company)

pascos88 — Wed, 24 Feb 2016 15:22:37 GMT

Hello Alessandro thanks for the response,

but with this formula I will have just a value, but I need to divide this count(Company) that have a value with

sum([KWh Real]/kWp).

pascos88 — Wed, 24 Feb 2016 15:30:37 GMT

Thanks Sunny

sunny_talwar — Wed, 24 Feb 2016 15:31:58 GMT

No problem