Straight table issue

kamalqlik — Tue, 15 Jul 2014 03:45:56 GMT

Hi Friends,

I have a table as mention below.

load * inline [

    SPD, SRD, SKU, SRD Selling, SRD Actual Sales

    A,X,19300-B,100,70

    A,X,19300-W,110,130

    A,Y,19300-B,200,70

    A,Y,19300-W,50,30

    A,Y,19300-R,0,130

];

I want the solution as like this to be calculated in Pivot table:

1)An expression ACC that will give the result in the following calculation as per excel...(IFERROR(Max(1-ABS(D-E)/D,0),0).

In case of Qlikview we have to use fabs in place abs...so the expression will be something like this

num(if(sum([SRD Selling])=0,0,max(1-fabs([SRD Selling]-[SRD Actual Sales])/[SRD Selling])),'#,##0%')

But it will give the correct result for all row but not for the last row ,as it has [SRD Selling] value as 0.So what I want is that whenever SRD Selling is having 0 value it should gives the value as o.

2) Second what I want is that...

After getting expression values ACC we want the result as..

SPD, SRD, SKU, SRD Selling, SRD Actual Sales,ACC

A,X,19300-B,100,70,70%

A,X,19300-W,110,130,82%

A,Y,19300-B,200,70,35%

A,Y,19300-W,50,30,60%

A,Y,19300-R,0,130,0%.

So new expression will be for SRD X forecast...which will be..

100*70+110*130/100+110=76%

and for y SRD it will be 40%.

Please provide solution for this.

Regards

Kamal

topic Straight table issue in QlikView

Straight table issue