Date Calculation

[#1]

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

[vikasmahajan]

like this ?

Vikas

[miguelbraga]

Take a look at this article:

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

Thanks,

MB

[#4]

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

[jonas_rezende]

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!

[#6]

Thanks Jonas Melo,

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

[jonas_rezende]

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.

[#8]

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.

[jonas_rezende]

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.