topic Date Calculation in QlikView

Date Calculation

Thu, 21 Jan 2016 10:28:17 GMT

Hi All I have Invoice date, Sale date and ID , now i need a table with weekend date, Invoice date, Sale date, ID.

Ex:

invoice date 17-Dec-2015 , Sale Date 18-Dec-2015

invoice date 18-Dec-2015 , Sale Date 19-Dec-2015

invoice date 15-Dec-2015 , Sale Date 17-Dec-2015

invoice date 19-Dec-2015 , Sale Date 29-Dec-2015

if I select week end Date 20-Dec-2015 i would like to see only

invoice date 17-Dec-2015 , Sale Date 18-Dec-2015

invoice date 18-Dec-2015 , Sale Date 19-Dec-2015

invoice date 15-Dec-2015 , Sale Date 17-Dec-2015

invoice date 19-Dec-2015

how do I get these days.

please find the sample data in the attachment.

swuehl‌

maxgro‌

hic‌

jagan‌

Thanks

John

Re: Date CalculationDate Calculation

vikasmahajan — Thu, 21 Jan 2016 11:50:37 GMT

like this ?

Vikas

Re: Date CalculationDate Calculation

miguelbraga — Thu, 21 Jan 2016 11:54:15 GMT

Take a look at this article:

https://community.qlik.com/blogs/qlikviewdesignblog/2015/09/28/dates-in-set-analysis

Thanks,

Re: Date CalculationDate Calculation

Thu, 21 Jan 2016 12:14:50 GMT

Hi Vikas in the sales Date section I don't want to see out out of weekend dates

Re: Date Calculation

jonas_rezende — Thu, 21 Jan 2016 12:25:47 GMT

Hi, johnDawson.

I created an example that contain Saturday and Sunday about sales date, using Weekend() function.

Community:

LOAD

Date([invoice date]) as [invoice date],

Date([sale date]) as [sale date],

ID,

Weekend([sale date],0,-1) as Saturday,

Weekend([sale date]) as Sunday

FROM

[..\Users\jonas.rezende\Downloads\Community11.xlsx]

(ooxml, embedded labels, table is TB09_20160121_021417);

It work according with the select in listbox Saturday or Sunday.

The following attached file.

Hope this helps!

Re: Date Calculation

Thu, 21 Jan 2016 12:55:17 GMT

Thanks Jonas Melo,

No luck with app, Please see the above image , I need the marked selections for that selection.

Re: Date Calculation

jonas_rezende — Thu, 21 Jan 2016 13:36:31 GMT

I understand you want to filter the sales that took place on the Sunday following the generation of the invoice. Case affirmative, use if(). Example.

if(Date([sale date]) <= Weekend([invoice date])

,Weekend([invoice date])

) as Sunday

Otherwise, the business rule.

Re: Date Calculation

Thu, 21 Jan 2016 14:24:52 GMT

Thanks Jonas Melo,

You see there in the Invoice dates we are not getting 3 days

14/122015

19/12/2015

20/12/2015

I need to see those days as well.

Re: Date Calculation

jonas_rezende — Fri, 22 Jan 2016 12:21:34 GMT

Please, detail the rules for best understanding. Include, example with at least three dates differents of weekend, and show the result desired.

Regards,

Jonas Melo.