Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

Differenc between dates

Hi community,

i want the difference of to dates with different values in a other coloum.

so for excample i have

article     info     date

apple     start     1/2/2012

cheese   start     3/5/2013    

cheese     end     6/7/2013    

apple     end     12/2/2016

and i want the difference between apple start and end.

So i have the following command for the date differenz:

date((date(Datum_VD,'YYYY/MM/DD')-date(Datum_VD,'YYYY/MM/DD')),'DD')

but i dont know how to say i want it for apple or cheese.

Thx for help

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Differenc between dates

Sorry, the source (resident) must be of course before the condition (where):

temp:

NOCONCATENATE load

     Artikelnummer,Datum as Wareneingang_datum

Resident InitialLoad

where "Bezeichnung Buchungsart" ='Wareneingang';

- Marcus

6 Replies
MVP & Luminary
MVP & Luminary

Re: Differenc between dates

You could do it within a sorted resident load per Peek() or Previous() ? or you joined both dates together like:

temp:

Load article, date as startdate From xyz where info = 'start';

     left join

Load article, date as enddate From xyz where info = 'end'

final:

Load article, startdate, enddate, enddate - startdate as diffdate resident temp;

drop table temp;

- Marcus

Re: Differenc between dates

May be this:

Table:

LOAD * Inline [

article,     info,     date

apple,     start,     1/2/2012

cheese,   start,     3/5/2013   

cheese,     end,     6/7/2013   

apple,     end,     12/2/2016

];

NewTable:

LOAD article,

  info,

  date,

  If(article = Peek('article'), Interval(date - Peek('date'), 'D')) as Difference

Resident Table

Order By article, date;

DROP Table Table;

Not applicable

Re: Differenc between dates

sorry i delete all answers my fault.

but now i have this error:

Der folgende Fehler ist aufgetreten:

Garbage after statement

Der Fehler ist hier aufgetreten:

temp: NOCONCATENATE load Artikelnummer,Datum as Wareneingang_datum where "Bezeichnung Buchungsart" ='Wareneingang' Resident InitialLoad

Highlighted
MVP & Luminary
MVP & Luminary

Re: Differenc between dates

Sorry, the source (resident) must be of course before the condition (where):

temp:

NOCONCATENATE load

     Artikelnummer,Datum as Wareneingang_datum

Resident InitialLoad

where "Bezeichnung Buchungsart" ='Wareneingang';

- Marcus

Not applicable

Re: Differenc between dates

Hi Marcus,

i run around in my data and had another idea for something i can display.

What about a KPI showing the timerange in days an article was in store from delivery to the store till it was

sold.

So we have articlenumber, amount,sold/delivery, date.

The thing is that there can be 2 deliveries of a product before even one sales.

But with diffdate i only get the datedifferenc to the last deliverie of the same product.

So maybe create a var with the amount coming in from delivery and use this as the delivery date for the diffdate expression as long its not 0.And when its 0 switch to the next delivery date, so we allways have the right date.

And an if  there is no delivery date before the sold-date i want to take the start date of the timerange.

The problem here, again i have a great idea but no clue about how i can achiev this in the right way.

So maybe u can give me some tipps.

Thanks for help in advance

-Eric

Not applicable

Re: Differenc between dates

Hi Sunny ,

u helped me also a lot maybe u have an idea on how to solve my new problem under Marcus comment.

Thx in advance

-Eric