topic Compare one column each cell value with next column accumulated values - coverage ratio in QlikView

Compare one column each cell value with next column accumulated values - coverage ratio

muhammadraza — Wed, 11 Feb 2015 08:35:03 GMT

Hello Guys,

I am stuck with some requirement, the image below explains the concept, PFA are the qvw and excel file. I want to achieve the same thing into Qlikview table.

Best Regards,

Muhammad Raza

Re: Compare one column each cell value with next column accumulated values - coverage ratio

Gysbert_Wassenaar — Wed, 11 Feb 2015 10:32:25 GMT

See attached qvw. Maybe that helps.

Re: Compare one column each cell value with next column accumulated values - coverage ratio

robert_mika — Wed, 11 Feb 2015 10:34:48 GMT

Could you share your Expression please?

I'm on PE.

Re: Compare one column each cell value with next column accumulated values - coverage ratio

Gysbert_Wassenaar — Wed, 11 Feb 2015 10:45:08 GMT

It's all done in the script:

T1:
LOAD Date(date) as date,
    [closing stock],
    forcast
FROM [comm151561.xlsx] (ooxml, embedded labels, table is Sheet2);

join

LOAD date as date_x, forcast as forcast_x
Resident T1;

T2:
LOAD *, if(stock_run >= 0,1, if(previous(stock_run) > 0, previous(stock_run)/forcast_x , 0)) as coverage;
LOAD *, rangesum([closing stock],-forcast_run) as stock_run;
LOAD *, if(date = peek(date), rangesum(forcast_x,peek(forcast_run)),forcast_x) as forcast_run
Resident T1
where date_x > date
order by date, date_x;

drop table T1;

T3:
LOAD date, [closing stock], forcast, sum(coverage) as coverage_ratio
Resident T2
group by date,[closing stock], forcast;

drop table T2;