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,
SM
|
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;
|
