Display open item accumulation

Anonymous — Mon, 19 Feb 2018 13:16:33 GMT

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

Re: Display open item accumulation

jwjackso — Mon, 19 Feb 2018 20:52:26 GMT

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

Re: Display open item accumulation

Anonymous — Tue, 20 Feb 2018 11:20:05 GMT

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;

topic Re: Display open item accumulation in QlikView

Display open item accumulation

Re: Display open item accumulation

Re: Display open item accumulation