topic Re: Straight Table Issue in QlikView

Straight Table Issue

kamalqlik — Tue, 15 Jul 2014 03:50:23 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

Re: Straight Table Issue

PrashantSangle — Tue, 15 Jul 2014 04:40:16 GMT

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

Re: Straight Table Issue

PrashantSangle — Tue, 15 Jul 2014 04:42:50 GMT

Hi,

To achieve 2nd expression.

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

Regards,

Re: Straight Table Issue

kamalqlik — Tue, 15 Jul 2014 05:45:13 GMT

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

Re: Straight Table Issue

kamalqlik — Tue, 15 Jul 2014 06:10:56 GMT

Hi Friends,

I am still looking for the solution.

Regards,

Kamal Naithani

Re: Re: Straight Table Issue

PrashantSangle — Tue, 15 Jul 2014 15:11:33 GMT

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