Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gino2780
Contributor

Calculate Date1 > Date2

Hello dear community members,

howe can i generate a new dimension or flag in the script that can show me something like this:

Show me when "2. Datum Faktura"  (01.02.2017) is older than "3. Datum Faktura" (08.02.2017)

Example data:

  

ID1. Datum Faktura2. Datum Faktura3. Datum FakturaSum(Fakturabetrag)
9508.02.2017--3.921,50 €
9505.01.2018--1.602,00 €
9512.01.2018--1.602,00 €
95-01.02.2017-3.921,50 €
95--08.02.2017-3.921,50 €
95--12.01.2018-1.602,00 €

Greets

GA

Tags (1)
1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: Calculate Date1 > Date2 or

make sure to evaluate and format your date fields correctly and try this as a second reload on your table:

FINAL_TABLE

LOAD *,if(Peek([2. Datum Faktura])< [3. Datum Faktura],'NOT YET FACTURED') as FLAG

Resident YOUR_TABLE;

10 Replies
YoussefBelloum
Esteemed Contributor

Re: Calculate Date1 > Date2 or

Hi,

what is the comparison rule ? always compare the lastest "3. Datum Faktura" value with the lastest "2. Datum Faktura" value ?

consultant_bi
Contributor

Re: Calculate Date1 > Date2 or

Hello

try this

Table:

load * inline

[ID,DATUM_1,DATUM_2,DATUM_3

1,23/01/2010,23/01/2011,23/01/2012

2,23/01/2010,23/01/2011,22/01/2011

3,23/01/2010,23/01/2011,23/01/2015

];

TABLE2:

load

*,if (DATUM_2>DATUM_3,1,0) as Flag

resident Table;

drop table Table;

gino2780
Contributor

Re: Calculate Date1 > Date2 or

Good question, i forgot to point that out.

Actually "2. Datum Faktura" is the final invoice.

"3. Datum Faktura" is a correction invoice.

So when 3. comes in the timeline after 2. i have to consider it as not yet finally invoiced, that's the main purpose of the flag.

YoussefBelloum
Esteemed Contributor

Re: Calculate Date1 > Date2 or

is this what you need ?

flag.png

gino2780
Contributor

Re: Calculate Date1 > Date2 or

I've already tried this, unfortunately i get a "invalid expression"-error after data load:

This is the script:

Faktura:

LOAD

    "PipelineID" as [ID],

    "Pipeline ID:Kostenvoranschlag",

    Fakturabetrag,

    "Datum Faktura",

    Erstellt_Faktura,

    "Erstellt von_Faktura",

    Geändert_Faktura,

    "Geändert von_Faktura",

    "SAP Belegnummer",

    Fakturaart,

    "1. Faktura",

    "2. Faktura",

    "3. Faktura",

    if([1. Faktura]= '1. Zwischenfaktura' and [Datum Faktura]<>'', [Datum Faktura]) as [1. Datum Faktura],

    if([2. Faktura]= '2. Endfaktura' and [Datum Faktura]<>'', [Datum Faktura]) as [2. Datum Faktura],

    if([3. Faktura]= '3. Korrektur' and [Datum Faktura]<>'', [Datum Faktura]) as [3. Datum Faktura],

  

    __KEY_data_Faktura

FROM [lib://K Netzlaufwerk BD/Export\Faktura.qvd]

(qvd);

Left Join (Faktura)

LOAD [ID],

If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([2. Faktura]) = '2. Endfaktura', 1, 0) as endfakturiert,

If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([2. Faktura]) = '2. Endfaktura' and MaxString([3. Faktura]) = '3. Korrektur', 1, 0) as "korrigiert fakturiert",

If(MaxString([1. Faktura]) = '1. Zwischenfaktura' and MaxString([3. Faktura]) = '3. Korrektur', 1, 0) as "korrigiert zwischenfakturiert",

If(MaxString([2. Faktura]) <> '2. Endfaktura', 1, 0) as "nf",

If ([2. Datum Faktura]>[3. Datum Faktura], 1, 0) as flag  //--> this line results in the "invalid expression" error //

Resident Faktura

Group By [ID];

gino2780
Contributor

Re: Calculate Date1 > Date2 or

Yes, Yousseff. Although the second "3. Datum Faktura" (12.01.2017) should not be considered in the flag, because it happened before 01.02.2017.

YoussefBelloum
Esteemed Contributor

Re: Calculate Date1 > Date2 or

My mistake, I was reading it 12.02.2018..

YoussefBelloum
Esteemed Contributor

Re: Calculate Date1 > Date2 or

make sure to evaluate and format your date fields correctly and try this as a second reload on your table:

FINAL_TABLE

LOAD *,if(Peek([2. Datum Faktura])< [3. Datum Faktura],'NOT YET FACTURED') as FLAG

Resident YOUR_TABLE;

gino2780
Contributor

Re: Calculate Date1 > Date2

Hello Youssef,

this worked. Thank you!

Greets,

GA