Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Have you tried something like this: Sum({$<ClearingDate={'Null'}>} Amount}
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;