topic Re: SUM for certain time period in QlikView

SUM for certain time period

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: SUM for certain time period

stabben23 — Mon, 25 Jun 2018 06:59:01 GMT

Hi,

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

Re: SUM for certain time period

sculptorlv — Mon, 25 Jun 2018 07:59:35 GMT

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

Re: SUM for certain time period

stabben23 — Mon, 25 Jun 2018 08:36:47 GMT

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;

Re: SUM for certain time period

jyothish8807 — Mon, 25 Jun 2018 08:56:49 GMT

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,

Re: SUM for certain time period

sculptorlv — Mon, 25 Jun 2018 08:59:08 GMT

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

Re: SUM for certain time period

sculptorlv — Mon, 25 Jun 2018 09:02:27 GMT

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

Re: SUM for certain time period

jyothish8807 — Mon, 25 Jun 2018 09:06:24 GMT

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

Re: SUM for certain time period

jyothish8807 — Mon, 25 Jun 2018 09:09:49 GMT

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

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

Br,

Re: SUM for certain time period

ruanhaese — Mon, 25 Jun 2018 09:17:13 GMT

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)))

Re: SUM for certain time period

sculptorlv — Mon, 25 Jun 2018 09:32:36 GMT

Range funcions really can help.

Can you please help me with a little bit more diffucutl problem?

I have Date, TransactionNumber and TransactionType for each row.

I need to find the Date (not the Quantity) within previous mentioned time periods where TransactionType =1 and TransactionNumber is MAX (= the last one).

For example, the results might be:

MonthName OrderDate

May 2014 12.05.2014

Jun 2014 12.05.2014 (thre were no transactions in June, but the last transaction is on 12th of May)

Jul 2014 17.07.2014

Re: SUM for certain time period

jyothish8807 — Mon, 25 Jun 2018 09:46:29 GMT

Hi Ruslans,

Can you please share more realistic data ? That wuld be more helpful to understand.

Br,

Re: SUM for certain time period

sculptorlv — Mon, 25 Jun 2018 09:58:44 GMT

Yes sure..

Transaction type 1 - is device install to client, Transaction type 0 - device is returned to warehouse.

I need to calculate, how many devices are total on clients for each month (at the end).

Thus, I try this formula:

rangecount(above(count(FA_Number),0,RowNo()), FA_Movement_To_Type = 1 AND FA_Movement_Posting_Date = MAX(FA_Movement_Posting_Date) )

But is doen't work correctly..

Re: SUM for certain time period

jyothish8807 — Mon, 25 Jun 2018 12:47:12 GMT

May be something like this:

Re: SUM for certain time period

sculptorlv — Mon, 25 Jun 2018 13:19:45 GMT

Thank you for this information.

Can you please alse tell me, how I can put limitation in equation?

I need to take into account only last reccord for whole period for each device.

Example data:

12.05.2017

15.05.2017

03.07.2017

The result:

for May, the date must be taken in account is 15.05.2017

for June, the date mast be taken in account is 15.05.2017

for July, the date mast be taken in account is 03.07.2017

Re: SUM for certain time period

jyothish8807 — Tue, 26 Jun 2018 04:31:32 GMT

Hi Ruan,

How are you planning to tackle the below situation ? which max date should be considered ?

Example data:

12.05.2017 --> FA_Movement_To_Type = 1

15.05.2017 --> FA_Movement_To_Type = 0

Br,

Re: SUM for certain time period

sculptorlv — Tue, 26 Jun 2018 04:33:49 GMT

I will take the last date 15.05.2017 and will know, that there is NO device on client (because 0 menas the final action was to remove from client)

Re: SUM for certain time period

jyothish8807 — Tue, 26 Jun 2018 05:31:45 GMT

Try this Ruslans,

Hope you might get some ideas

Br,

Re: SUM for certain time period

sculptorlv — Tue, 26 Jun 2018 05:39:45 GMT

But how can I ignore rows with incorrect Transaction type?

For June, the Cum Sum have to be 1. Because, there is no rows with transaction tyep 1 in June.

Re: SUM for certain time period

jyothish8807 — Tue, 26 Jun 2018 05:53:41 GMT

You can simply put a filter like this:

RangeCount(above(count({<TranType={'1'}>}Sale),0,RowNo()))

Br,