topic Re: Issue in Accumulate in QlikView

Issue in Accumulate

Fri, 22 Feb 2013 07:45:10 GMT

Hi,

Please find the attachment which is showing me Accumulate values of volume [ Month Year] Jan2013, Feb2013 and Mar2013 but

i want only Jan and Feb becuase volume has only two months, kindly guide me the solution.

Regards,

Zain.

Re: Issue in Accumulate

somenathroy — Fri, 22 Feb 2013 08:00:16 GMT

You may use Above() function like below code snipet with LOAD statement:

if(Above([ Month Year]) = [ Month Year], Accumulated field - Above(Accumulated field), Accumulated field) as MonthWiseValue

Regards,

Som

Re: Issue in Accumulate

Fri, 22 Feb 2013 08:19:04 GMT

Dear above() function isnt working in script, i used previous() instead of above() but it isnt working.

Regards

Zain.

Re: Issue in Accumulate

somenathroy — Mon, 25 Feb 2013 07:05:31 GMT

Thanks for your correction.

Have a look the below code, hope it may helps:

LOAD Right([Month Year],4) as year, Left([Month Year],3) as month, Date(Date#([Month Year],'MMMYYYY'),'DD/MM/YYYY') as MonthStart ,volume as Accumulated_Volume;

LOAD * Inline

[Month Year, volume

Jan2013,10

Feb2013,25

Mar2013,25];

NoConcatenate

LOAD *, if(IsNull(Previous(year)),Accumulated_Volume , Accumulated_Volume - Previous(Accumulated_Volume)) as MonthWise_Volume

Resident A Order by year,MonthStart;

DROP Table A;

Regards,

Som

Re: Issue in Accumulate

Mon, 25 Feb 2013 07:21:40 GMT

Dear Som, thanks for your reply i resolved my problem through script but not your one, basiclly i want to know how to handle this issue in expression.

Regards,

Zain.

Re: Issue in Accumulate

er_mohit — Mon, 25 Feb 2013 07:37:12 GMT

try this:

expression---> rangesum(above(sum(volume),0,RowNo()))

[ Month Year] adding your dimension

Re: Issue in Accumulate

rob_greene — Mon, 25 Feb 2013 08:10:55 GMT

Hi Zain,

Can you share how you resolved this in the script please, I am trying to do this too.

I have done it in my table using the above function, but need it in the script.

if(order_id=Above( Total order_id),Amount-Above(Total Amount),0))

Many thanks

Rob

Re: Issue in Accumulate

Mon, 25 Feb 2013 08:15:20 GMT

Dear Rob,

Please find the attachment.

Regards,

Zain.

Re: Issue in Accumulate

Mon, 25 Feb 2013 08:19:14 GMT

Dear ER.Mohit,

I did but its give me continuing line for all months, i have attached the application (first one) kindly apply on it and send to me back, i need to show only jan and feb 2013 data.

Re: Issue in Accumulate

rob_greene — Mon, 25 Feb 2013 09:55:38 GMT

Many thanks Zain,

I have done this now

IF( Tranche_ID=Previous(Tranche_ID)

AND order_id=Previous(order_id)

AND [Order_Version]<>Previous(Order_Version),

Amount - Previous(Amount),Amount ) AS Delta2

It worked a treat.

Rob

(note:above function in script)