Doubt on Filtering not null values

[icaro_povoa]

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

[sergio0592]

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

[vishsaggi]

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

[malini_qlikview]

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.