New User Question: Cumulative Sum as Dimension

Wed, 25 Nov 2020 16:16:04 GMT

Hi - I'm new to QlikView and trying to come up the learning curve here for a water well application.

I have monthly water production volumes data in from excel, uniquely identified by well number, completion name and yearmonth, and I want to be able to produce a chart the has the monthly water volumes as the expression, and cumulative monthly water volume as the dimension (this effectively removes calendar downtime). I think that this would be easier if I reversed the the dimension/expression and just used full aggregation however I want to be able to put the ascending cumulative volumes across the x axis.

I have tried two methods (after much research) this far:

1) using a calculated dimension with RangeSum(Above(TOTAL Sum(MON_WTR_PROD_VOL), 0, RowNo())) and MON_WTR_PROD_VOL as the expression however this does not plot for reasons that exceed my current understanding of QlikView. Does anyone have any pointers?

2) using a load script (below) - this works but calculates the cumulative total as-loaded (which is reverse-chronologically ie most recent to least recent month - see screengrab) whereas I would like it calculated chronologically (least recent month to most recent month). I thought the ORDER BY command would ensure this but it only orders the resulting output data.

Is there anyway of ensuring the data is ordered in a specific way before the calculation in the load script? or do I need to sort the data before I load it into QlikView?

Or is there an easier way of accomplishing the end result?

Thanks in advance,

MainTable:

LOAD COMPLETION_NAME,

     PRODUCTION_DATE,

     PRODUCTION_YEAR,

     PRODUCTION_MONTH,

     MakeDate(PRODUCTION_YEAR, PRODUCTION_MONTH) as PRODUCTION_QVDATE,

     DAYS_ON_PROD,

     MON_WTR_PROD_VOL,

     API_WELL_NUMBER,

FROM

(ooxml, embedded labels);

TempTable:

LOAD COMPLETION_NAME,

     API_WELL_NUMBER,

     MON_WTR_PROD_VOL,

     PRODUCTION_DATE,

     If(API_WELL_NUMBER=Previous(API_WELL_NUMBER)and COMPLETION_NAME = Previous(COMPLETION_NAME), MON_WTR_PROD_VOL + If(IsNull(Peek(MON_WTR_CUM_PROD_VOL)), 0, Peek(MON_WTR_CUM_PROD_VOL)), MON_WTR_PROD_VOL) as MON_WTR_CUM_PROD_VOL

     RESIDENT MainTable

ORDER BY API_WELL_NUMBER ASC, COMPLETION_NAME ASC, PRODUCTION_DATE DESC;

Re: New User Question: Cumulative Sum as Dimension

Gysbert_Wassenaar — Tue, 22 Mar 2016 08:09:18 GMT

ORDER BY API_WELL_NUMBER ASC, COMPLETION_NAME ASC, PRODUCTION_DATE DESC

You're sorting by production date descending. That's why you get the cumulative volume backwards.

Re: New User Question: Cumulative Sum as Dimension

Fri, 25 Mar 2016 02:17:25 GMT

Thanks Gysbert - appreciate the help, that solved it.

topic Re: New User Question: Cumulative Sum as Dimension in QlikView

New User Question: Cumulative Sum as Dimension

Re: New User Question: Cumulative Sum as Dimension

Re: New User Question: Cumulative Sum as Dimension