Display open item accumulation

[Anonymous]

Dear Qlikview Experts,

as I am unexperienced and my deadline is approaching, I ask for help. I will display my problem in a simple way.

I have a data set of invoices. They have an invoice number as key ID, a document date, an amount and may have a clearing date.

I want to display on a timeline the open amount. An invoice is open from the day of the document date until the clearing date.

I somehow managed to create a master calendar. Of course I can't just create a diagram and accumulate values by document date as the invoices are cleared and then need to be removed from accumulation. Maybe that is an easy one and can simply be solved by an expert.

Please find attached my example.

Thanks, Christof

[jwjackso]

Have you tried something like this: Sum({$<ClearingDate={'Null'}>} Amount}

[Anonymous]

Hi Jerry, it is more complicated than that. I do not want to search items that have no Clearing Date.

Let's say, I have an invoice from last year

Doc Date 10.10.2017

Clearing Date 14.10.2017

Amount 100 Euro

That means that on my timeline this Amount adds up to the sum of Open Items from 10.10. - 13.10 but needs to be removed on day 14.10.

I think I solved it now...

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';

SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='de-DE';

Invoices:

Load * Inline [

DocNr|DocDate|ClearingDate|Amount

101|20.11.2010|25.11.2010|150,00

102|19.11.2010|28.11.2010|600,00

103|02.12.2010||130,00

104|02.12.2010|10.12.2010|260,00

105|10.12.2010|30.12.2010|410,00

106|07.12.2010||1000

] (delimiter is '|');

NoConcatenate

Clearing:

Load

    ClearingDate as MasterDate,

    DocNr,

    Amount * -1 as Amount

Resident Invoices

Where (ClearingDate <> '')

;

Temp:

Load

    Min(DocDate) as MinDocDate,

    Max(DocDate) as MaxDocDate

Resident Invoices;

    Let varMinDocDate = Num(Peek('MinDocDate', 0, 'Temp')); 

    Let varMaxDocDate = Num(Peek('MaxDocDate', 0, 'Temp')); 

DROP Table Temp;

TempCalendar: 

LOAD 

    $(varMinDocDate) + Iterno()-1 As Num, 

    Date($(varMinDocDate) + IterNo() - 1) as TempDate 

    AutoGenerate 1 While $(varMinDocDate) + IterNo() -1 <= $(varMaxDocDate);

MasterCal:

Load

TempDate as MasterDate

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar; 

Outer Join (MasterCal)

Load

DocDate as MasterDate,

DocNr,

Amount

Resident Invoices;

DROP Table Invoices;

Outer Join (MasterCal)

Load

MasterDate,

DocNr,

Amount

Resident Clearing;

Drop Table Clearing;

NoConcatenate

MasterCal2:

Load

MasterDate,

DocNr,

If(Amount > 0,'Invoice',If(Amount<0,'Clearing',''))

            as ItemType,

If (isnull(Amount),'0',Amount)

            as Amount,

If(RowNo()=1,If(isnull(Amount),'0',Amount) , peek(NetAmount) +

If(isnull(Amount),'0',Amount) )

            as NetAmount

Resident MasterCal

Order By MasterDate;

Drop Table MasterCal;