Accumulate data to some point in bar chart

[retko1985]

Hello,

I have data something like this:Acc

What I want is this:

Therefore all targets before first day where I have data should accumulate. 

Also on last bar (day of the month!), I take targets before and after that bar, to accumulate, and display on that bar (day).

Can someone help me with this?

Can be set analysis or scripting behind, or maybe window functions in SQL, don't care 🙂

Thank you very much!

[tresesco]

Try like:

If(Sum(Value)>0, RangeSum(Above(Sum(Target),0,RowNo(TOTAL))))

[Frank_Hartmann]

can u explain why your Targetvalue for Day7 = 60?
I would expect Day7 = 70!

[sunny_talwar]

Or even this (used Tresesco's solution and made modifications)

If(Sum(Value) > 0, RangeSum(Above(Sum({<Day>}Target), 0, RowNo(TOTAL))))

This will allow you to select a day and still see the Target

[retko1985]

Hi,

Unfortunately this is not correct.

Here is why Target for second bar is 60, not 70:

A little more explanation is like this. Value is some accumulation on last week, can be 5 to seven days back, but we have targets for each day (same number for whole month). Now we want to accumulate targets for the same period as is value accumulated. That's why we need to take second part of days, not from starting day of the month. And because it it last BAR of that month I need to take targets after it to the end of the month also.

Thank you.

[sunny_talwar]

But how do we determine that value 50 belongs to day 5 till 10? What is stopping us from thinking that 35 was from 1 till 5 and then 50 was from 6 till 10? I mean is there a week field which was not provided with the data?

[retko1985]

Our data source has column date, like here. And for day 4 there is a transaction row having value 35. Business said that this number is accumulation from start of the month till the day written for this row.
Second row has date 7. And there is no row till the end of month. So we can say that this value is for days 5, 6, 7. Bud because it is last value for that month, we want to take targets which are after day 7 till the end of that month, and show it for that day when data came.

Thanks

[sunny_talwar]

So, if your sample data was like this

Have:
LOAD * INLINE [
    Day, Value, Target
    1, 0, 10
    2, 0, 10
    3, 0, 10
    4, 35, 10
    5, 0, 10
    6, 0, 10
    7, 50, 10
    8, 0, 10
    9, 0, 10
    10, 0, 10
    11, 60, 10
    12, 0, 10
    13, 0, 10
];

The expected output will be this?

LOAD * INLINE [
    Day_Want, Value_Want, Target_Want
    1, 0, 0
    2, 0, 0
    3, 0, 0
    4, 35, 40
    5, 0, 0
    6, 0, 0
    7, 50, 30
    8, 0, 0
    9, 0, 0
    10, 0, 0
    11, 60, 60
    12, 0, 0
    13, 0, 0
];

[sunny_talwar · Accepted solution]

There might be a better way to do this... but may be do this in the script like this

Table:
LOAD *,
	 MonthName(Date) as MonthYear,
	 Day(Date) as Day;
LOAD * INLINE [
    Date, Value, Target
    1/1/2018, 0, 10
    1/2/2018, 0, 10
    1/3/2018, 0, 10
    1/4/2018, 35, 10
    1/5/2018, 0, 10
    1/6/2018, 0, 10
    1/7/2018, 50, 10
    1/8/2018, 0, 10
    1/9/2018, 0, 10
    1/10/2018, 0, 10
    1/11/2018, 60, 10
    1/12/2018, 0, 10
    1/13/2018, 0, 10
    1/14/2018, 0, 10
    1/15/2018, 0, 10
    1/16/2018, 0, 10
    1/17/2018, 0, 10
    1/18/2018, 0, 10
    1/19/2018, 0, 10
    1/20/2018, 0, 10
    1/21/2018, 0, 10
    1/22/2018, 0, 10
    1/23/2018, 0, 10
    1/24/2018, 0, 10
    1/25/2018, 0, 10
    1/26/2018, 0, 10
    1/27/2018, 0, 10
    1/28/2018, 0, 10
    1/29/2018, 0, 10
    1/30/2018, 0, 10
    1/31/2018, 0, 10
    2/1/2018, 0, 10
    2/2/2018, 0, 10
    2/3/2018, 0, 10
    2/4/2018, 0, 10
    2/5/2018, 0, 10
    2/6/2018, 0, 10
    2/7/2018, 0, 10
    2/8/2018, 0, 10
    2/9/2018, 40, 10
    2/10/2018, 0, 10
    2/11/2018, 0, 10
    2/12/2018, 0, 10
    2/13/2018, 0, 10
    2/14/2018, 0, 10
    2/15/2018, 50, 10
    2/16/2018, 0, 10
    2/17/2018, 0, 10
    2/18/2018, 0, 10
    2/19/2018, 0, 10
    2/20/2018, 0, 10
    2/21/2018, 0, 10
    2/22/2018, 60, 10
    2/23/2018, 0, 10
    2/24/2018, 0, 10
    2/25/2018, 0, 10
    2/26/2018, 0, 10
    2/27/2018, 0, 10
    2/28/2018, 80, 10
];

TempTable:
LOAD *,
	 If(MonthYear = Previous(MonthYear) and Previous(Value) = 0, RangeSum(Peek('CumTarget'), Target), Target) as CumTarget
Resident Table;

Left Join (TempTable)
LOAD MonthYear,
	 Max(New_CumTarget) as New_CumTarget,
	 Max(If(Value <> 0, Date)) as Date
Group By MonthYear;
LOAD MonthYear,
	 Date,
	 Value,
	 If(Floor(MonthEnd(MonthYear)) = Date, CumTarget) as New_CumTarget
Resident TempTable
Where (Floor(MonthEnd(MonthYear)) = Date) or Value <> 0;

FinalTable:
LOAD Date,
	 Value,
	 Target,
	 MonthYear,
	 Day,
	 RangeSum(If(Floor(MonthEnd(MonthYear)) = Date, 0, New_CumTarget), CumTarget) as Cum_Target
Resident TempTable;

DROP Tables Table, TempTable;

[retko1985]

Seems to work on example data 🙂 So I will test it on my data, and will let you know. Thank you very much for your help 🙂