Straight Table Issue

[kamalqlik]

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

[PrashantSangle]

Hi,

Try this in your 1st expression

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

Regards

[PrashantSangle]

Hi,

To achieve 2nd expression.

Look for below(), It might solve your problem.

Regards,

[kamalqlik]

Hi Max,

It is not working.

As still it is not giving result for

A,Y,19300-R,0,130

It must show 0%.

Regards

Kamal Naithani

[kamalqlik]

Hi Friends,

I am still looking for the solution.

Regards,

Kamal Naithani

[PrashantSangle]

Hi,

I am not understanding why 1st expression is not working at your end.

I am attaching my test application where i am implementing same Code as you provided.

Please find attachement

Regards