topic Re: Doubt on Filtering not null values in QlikView

Doubt on Filtering not null values

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

Hello Qlik Community,

I am new in Qlikview, and I am having troubles to create an Table.

On my data source I have something close to the following table

I would like to build an table with the status of planting being Planned as 100%. In other words, in the example I would

Sum(Acreage F1+F2.....F8) for Plan and would Sum(Acreage F1+F2...F4) for Actual. Dividing one by the other I would have the percentage of acreage that was actually planted.

The problem is that I have 700 fields and cannot sum them individually.

Is there a way to sum the acreage only from where the date is not null ? Can you help me with this expression?

Regards

Re: Doubt on Filtering not null values

sergio0592 — Tue, 11 Apr 2017 20:48:18 GMT

Is this that you're trying to achieve?? In front end, in a straight chart or in a text box (for KPI) you can use as expression %:

(sum({$<[Actual planned week]-= {""}>} TOTAL Average) / sum(TOTAL Average) )*100

Re: Doubt on Filtering not null values

vishsaggi — Tue, 11 Apr 2017 21:31:02 GMT

Extending Jean's expression May be this?

My Bad ignore, Jean's expression should work? What is your expected ouput?

= (Sum(Acreage)/Sum(Total {< ActualPlantingWeek -= {""}>} Acreage))

Re: Doubt on Filtering not null values

malini_qlikview — Wed, 12 Apr 2017 11:44:48 GMT

Hi ,

Check if the below helps,

Use Rangesum() to cumulative sum the values of acreage

The output is the below

Expressions:

Planned :Sum(Total Acreage)

Actual : If(Isnull([Actual Planting week])=0,Aggr(Rangesum(Above(Sum(Acreage),0,RowNo())),Field))

% : Actual/Planned

you can do the same calculation in your script as well.