Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

retko1985
Contributor II

Accumulate data to some point in bar chart

Hello,

I have data something like this:2018-12-03 10_40_27-QlikView x64 - [C__Users_pc00280_Desktop_QTests_accumulate data to some point in.pngAcc

 

What I want is this:2018-12-03 10_45_48-QlikView x64 - [C__Users_pc00280_Desktop_QTests_accumulate data to some point in.png

 

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!

1 Solution

Accepted Solutions

Re: Accumulate data to some point in bar chart

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;

View solution in original post

9 Replies
MVP
MVP

Re: Accumulate data to some point in bar chart

Try like:

If(Sum(Value)>0, RangeSum(Above(Sum(Target),0,RowNo(TOTAL))))
Frank_Hartmann
Honored Contributor II

Re: Accumulate data to some point in bar chart

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

Re: Accumulate data to some point in bar chart

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

image.png

Highlighted
retko1985
Contributor II

Re: Accumulate data to some point in bar chart

Hi,

 

Unfortunately this is not correct.

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

2018-12-03 13_37_47-QlikView x64 - [C__Users_pc00280_Downloads_accumulate data to some point in bar .png

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

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
Contributor II

Re: Accumulate data to some point in bar chart

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

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

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;

View solution in original post

retko1985
Contributor II

Re: Accumulate data to some point in bar chart

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 🙂