SUM for certain time period

[sculptorlv]

I have the following data:

Data               Quantity

01.01.2018     1

04.01.2018     15

06.03.2018     18

17.04.2018     3

How can I get cummulative sum for each month name (until Dec 2018)?

I need to get the following results:

MonthName     Sum

Jan 2018          16

Feb 2018          16

Mar 2018          34

Apr 2018           37

May 2018          37

..

Dec 2018          37

I hope for your help

[stabben23]

Hi,

The normal behavior is to Connect a calendar to Your fact table.

[sculptorlv]

Can you please provide any info or link how to do it?

[stabben23]

Like this for ex

Data               Quantity

01.01.2018     1

04.01.2018     15

06.03.2018     18

17.04.2018     3

FactTable:

Load

Data as %Date,

Quantity

From SourceTable

;

Calendar:
LOAD distinct
%Date,
date(floor(%Date)) as Date,
Week(%Date) as Week,
Year(%Date) as Year,
Month(%Date) as Month,
Day(%Date) as Day,
hour(%Date) as Hour,
Minute(%Date) as Minute,
WeekDay(%Date) as WeekDay,
'Q' & ceil(month(%Date)/3) as Quarter,
Date(MonthStart(%Date),'MMM-YYYY') as MonthYear,
Year(%Date)& num(Month(%Date),'00') as YearMonth,
Week(%Date) & '-' & Year(%Date) as WeekYear,
//======Flags========
inYeartoDate(%Date,today(1),0) * (-1) as YTD, // 1 for year to date
inYeartoDate(%Date,today(1),-1) * (-1) as LYTD,// last year to date
inMonthtoDate(%Date,today(1),0) * (-1) as MTD, // month to date
inMonthtoDate(%Date,today(1),-1) * (-1) as LMTD, // lastmonth to date
inMonthtoDate(%Date,today(1),-13) * (-1) as LMLY, //lastmonth last year
inWeek(%Date,today(1),0) * (-1) as thisWeek, // this week
if(date(floor(%Date))=date(today(1)-1),1) as Yesterday

Resident FactTable
Order by %Date ASC;

[jyothish8807]

Hi Ruslans,

You can do like this also:

Load

date(date#(Data,'DD.MM.YYYY'),'DD.MM.YYYY') as Data,

monthname(date(date#(Data,'DD.MM.YYYY'),'DD.MM.YYYY')  as MonthYear,

Quantity

from <>;

Br,

KC

[sculptorlv]

Sorry, I can't get the idea, how it can calculate cumulative sums..

for instance in March, I have to sum three quantities from January, February and March

[sculptorlv]

Thank you for the code. I am not sure how to use these flags, like

inMonthtoDate(%Date,today(1),-1) * (-1) as LMTD, // lastmonth to date
inMonthtoDate(%Date,today(1),-13) * (-1) as LMLY, //lastmonth last year

[jyothish8807]

Hi Ruslans,

For commutative sum, you can use " full Accumulation" option.

Load

date(date#(Data,'DD.MM.YYYY'),'DD.MM.YYYY') as Data,

monthname(date(date#(Data,'DD.MM.YYYY'),'DD.MM.YYYY')  as MonthYear,

Quantity

from <>;

Create a straight chart,

dim: MonthYear

Exp: sum(Quantity)

Select full accumulation

[jyothish8807 · Accepted solution]

You an use this expression also, if you dont want to use "Full Accumulation"

rangesum(above(sum(Sale),0,RowNo()))

Br,

KC

[ruanhaese]

In the Load script add a field that refers to the Year-Month

E.g.

LOAD

...

,Date( MonthStart( Date#( Data, 'dd.MM.YYYY')), 'YYYY-MMM')  As CalenderPeriod

FROM ...

Then you can used that new dimension to show the sum of those values

(like in a bar chart or table).

To answer the cumulative part as well in Qlikview there is an option in the table settings

that can accumulate. In qlik sense, however, you would need to write a formulae:

RangeSum(Above(TOTAL Sum([Expression1]), 0, RowNo(TOTAL)))