Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have dataset which contains months and sales and some of the sales are Null.
I need that the null values to be assigned with the cumulative sum of previous values till that null value.
I provide the dataset below i that Sheet 2 is the sample.
Thanking You in advance,
This?
Load Month, Sales, If(Len(trim(Sales))=0,0,AccuSales) as AccuSales, If(Len(trim(Sales))=0, Peek(AccuSales), Sales) as NewSales ; Load *, RangeSum(Peek(AccuSales),Sales) as AccuSales Inline [ Month Sales Jan 210 Feb 250 Mar Apr 150 May 265 Jun Jul 154 Aug 145 Sep 415 Oct Nov 156 Dec ] (delimiter is spaces); Drop Fields AccuSales;
like this?
Load Month, Sales,AccuSales, If(Len(trim(Sales))=0, Peek(AccuSales), Sales) as NewSales ; Load *, RangeSum(Peek(AccuSales),Sales) as AccuSales Inline [ Month Sales Jan 210 Feb 250 Mar Apr 150 May 265 Jun Jul 154 Aug 145 Sep 415 Oct Nov 156 Dec ] (delimiter is spaces); Drop Fields AccuSales;
You are giving the rolling sum to previous also.
My scenario is in dataset if you observe I need the values of
Mar-460
Jun-415
Oct-714
Dec-156
This?
Load Month, Sales, If(Len(trim(Sales))=0,0,AccuSales) as AccuSales, If(Len(trim(Sales))=0, Peek(AccuSales), Sales) as NewSales ; Load *, RangeSum(Peek(AccuSales),Sales) as AccuSales Inline [ Month Sales Jan 210 Feb 250 Mar Apr 150 May 265 Jun Jul 154 Aug 145 Sep 415 Oct Nov 156 Dec ] (delimiter is spaces); Drop Fields AccuSales;
Thanks a lot
Can you explain me the logic ?
Sameer, I am bad at explaining in writing. If you could let me know exactly which section of the code you need the clarification on, it would be easier for me to explain.
Can we do the same with importing not by creating the Inline table?If yes, Then How?