topic Re: Accumulate data to some point in bar chart in QlikView

Accumulate data to some point in bar chart

retko1985 — Mon, 03 Dec 2018 09:56:52 GMT

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!

Re: Accumulate data to some point in bar chart

tresB — Mon, 03 Dec 2018 10:02:36 GMT

Try like:

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

Re: Accumulate data to some point in bar chart

Frank_Hartmann — Mon, 03 Dec 2018 10:27:27 GMT

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

Re: Accumulate data to some point in bar chart

sunny_talwar — Mon, 03 Dec 2018 12:18:42 GMT

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

Re: Accumulate data to some point in bar chart

retko1985 — Mon, 03 Dec 2018 12:46:13 GMT

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.

Re: Accumulate data to some point in bar chart

sunny_talwar — Mon, 03 Dec 2018 12:48:39 GMT

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?

Re: Accumulate data to some point in bar chart

retko1985 — Mon, 03 Dec 2018 14:34:35 GMT

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

Re: Accumulate data to some point in bar chart

sunny_talwar — Mon, 03 Dec 2018 14:41:50 GMT

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
];

Re: Accumulate data to some point in bar chart

sunny_talwar — Mon, 03 Dec 2018 15:07:45 GMT

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;

Re: Accumulate data to some point in bar chart

retko1985 — Mon, 03 Dec 2018 15:58:05 GMT

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 🙂