topic Re: Cummulating in Scrip with one Month value missing in QlikView

Cummulating in Scrip with one Month value missing

manish_kumar — Sat, 21 Jan 2017 08:44:01 GMT

I have a data with some valued on monthly basis. But for one month value is not there.

Thus when I cummulate the data in script it omits the row for the month which is not having the data. I need the cummulative value for that month also which is not having the value itself.

Original data

Month	Values
01-04-2015	80615
01-05-2015	74193
01-06-2015	74193
01-07-2015	74193
01-08-2015	74193
01-09-2015	74193
01-10-2015	78336
01-11-2015	74193
01-12-2015	74193
01-01-2016	74893
01-03-2016	8357

Desired result

Month	Cummuative Values
01-04-2015	80615
01-05-2015	154808
01-06-2015	229001
01-07-2015	303194
01-08-2015	377387
01-09-2015	451580
01-10-2015	529916
01-11-2015	604109
01-12-2015	678302
01-01-2016	753195
01-02-2016	753195
01-03-2016	761552

Re: Cummulating in Scrip with one Month value missing

Anil_Babu_Samineni — Sat, 21 Jan 2017 20:07:45 GMT

May be using, Full accumulation for Sum(Values) in expression Tab of Straight table, For ref please follow the image

Or May be expression

Rangesum(Below(Sum(Values),0),Above(Values,1,1000))

Re: Cummulating in Scrip with one Month value missing

manish_kumar — Sat, 21 Jan 2017 20:14:17 GMT

Thanks for the reply.

I need this work to be done in Back end only. Because I have to apply further logics on Cummulative values.

Re: Cummulating in Scrip with one Month value missing

rubenmarin — Sat, 21 Jan 2017 22:08:36 GMT

Hi Manish, having a calendar can help generating the missing months:

Data:

LOAD Date#(Month, 'DD-MM-YYYY') as Month, Values Inline [

Month, Values

01-04-2015, 80615

01-05-2015, 74193

01-06-2015, 74193

01-07-2015, 74193

01-08-2015, 74193

01-09-2015, 74193

01-10-2015, 78336

01-11-2015, 74193

01-12-2015, 74193

01-01-2016, 74893

01-03-2016, 8357

];

Calendar:

Load Date(AddMonths(MinMonth, IterNo())) as Month While MaxMonth >= AddMonths(MinMonth, IterNo());

LOAD Min(Month) as MinMonth,

Max(Month) as MaxMonth

Resident Data;

Maybe you need to uncheck the 'Supress zero values' option in presentation tab.

To know more about missing data: Generating Missing Data In QlikView

And about calendars: How to use - Master-Calendar and Date-Values

Re: Cummulating in Scrip with one Month value missing

ahaahaaha — Mon, 23 Jan 2017 13:36:44 GMT

It uses the principle of creating a Master-Calendar.

Month: //Generate the first day of the month date

LOAD

AddMonths(Date('01.04.2015'), RecNo() - 1) as Month

autogenerate 12;

Left Join //Left connection load the raw data

LOAD*Inline

[

Month, Values

01.04.2015, 80615

01.05.2015, 74193

01.06.2015, 74193

01.07.2015, 74193

01.08.2015, 74193

01.09.2015, 74193

01.10.2015, 78336

01.11.2015, 74193

01.12.2015, 74193

01.01.2016, 74893

01.03.2016, 8357

];

Table1: //Form a table with a cumulative sum

NoConcatenate

LOAD*,

Rangesum(Values, peek('CummuativeValues')) as CummuativeValues

Resident Month;

DROP Table Month;

The result is a table

If the raw data is loaded from an external source (for example, file xls), the end result can be formed at once by making a left connection with the accumulation the sum.

Regards

Andrey