Chart Expression Assistance

[Anonymous]

Hello!

I'm stuck on an expression for a chart I'm putting together.

Data is laid out as per the table below.  This is a very small subset.

PKT_CTRL_NBR	DIV	CHANNEL	CREATE_DATE	PRINT_DATE	UNITS
8370650	WHOLESALE	WHOLESALE	12/14/2015	01/13/2016	13
8370651	WHOLESALE	WHOLESALE	12/15/2015	01/06/2016	26
8370652	WHOLESALE	WHOLESALE	12/29/2015	01/04/2016	6581
8370653	RETAIL	DOS	12/29/2015	01/04/2016	4546
8370654	WHOLESALE	WHOLESALE	12/30/2015	01/04/2016	2167
8370655	RETAIL	DOS	12/30/2015	01/04/2016	2167
8370656	WHOLESALE	WHOLESALE	12/30/2015	01/05/2016	264
8370657	RETAIL	DOS	12/30/2015	01/08/2016	1996
8370658	WHOLESALE	WHOLESALE	12/31/2015	01/04/2016	1000
8370659	RETAIL	DOS	12/31/2015	01/04/2016	891
8370660	WHOLESALE	WHOLESALE	12/31/2015	01/08/2016	100
8370661	RETAIL	DOS	12/31/2015	01/08/2016	236
8370662	WHOLESALE	WHOLESALE	12/31/2015	01/12/2016	576
8370663	RETAIL	DOS	12/31/2015	01/13/2016	100
8370664	WHOLESALE	WHOLESALE	12/31/2015	01/13/2016	194
8370665	WHOLESALE	WHOLESALE	01/01/2016	01/04/2016	101
8370666	WHOLESALE	WHOLESALE	01/03/2016	01/04/2016	500
8370667	RETAIL	DOS	01/03/2016	01/04/2016	768
8370668	WHOLESALE	WHOLESALE	01/03/2016	01/05/2016	500
8370669	RETAIL	DOS	01/03/2016	01/05/2016	526
8370670	WHOLESALE	WHOLESALE	01/03/2016	01/06/2016	200
8370671	RETAIL	DOS	01/03/2016	01/06/2016	298
8370672	WHOLESALE	WHOLESALE	01/04/2016	01/04/2016	192
8370673	RETAIL	DOS	01/04/2016	01/05/2016	8426
8370674	WHOLESALE	WHOLESALE	01/04/2016	01/06/2016	1107
8370675	RETAIL	DOS	01/04/2016	01/13/2016	124
8370676	WHOLESALE	WHOLESALE	01/04/2016	01/20/2016	94
8370677	RETAIL	DOS	01/04/2016	01/26/2016	81

What I'm trying to show is how many UNITS were available to print each day. 

What I'd like to see is something like this:

PRINT_DATE	UNITS_CREATED	UNITS_PRINTED	UNITS_AVAILABLE
01/04/2016	10024	18913	33774

Then the pattern would repeat for each day.  The units available would be the total of units printed that day and units created but not yet printed.  Is this even possible?  Any help would be fantastic.

[Anonymous]

Hello,

Thanks for the sample, however this does not seem to work for other dates, as you'd have to select the exact CREATE dates for a given Print date.

What I'm hoping to achieve is the table posted above by Johnathan Dienst, but with all data still available.

[qliksus]

hi ,

Change the Expression as

sum({<Date=,Flag={'C'}>}UNITS)  and select the Full Accumulation Option

[jonathandienst]

kkkumar82 wrote:

Hi Jonathan,

Could you please elaborate

RangeSum(UnitsCreated, -Previous(UnitsPrinted), Peek(UnitsAvailable)) as UnitsAvailable 

The above line especially -Previous(UnitsPrinted)

This is a null-safe way of calculating:

     UnitsCreates - Previous(UnitsPrinted) + Peek(UnitsAvailable)

If any of the terms is null, the whole expression evaluates to null. RangeSum will treat a null in any individual term as zero, so the expression will evaluate correctly.

Previous = get the value from the previous record of the load's source

Peek = get the value from the previous record of the load's output