topic Re: Find Missing Date From Last Week in QlikView

Find Missing Date From Last Week

tamilarasu — Wed, 27 Jul 2016 02:07:01 GMT

Hello All,

I came across a scenario and I hope someone have a simple solution. I have a field named as "Date" and the dates are populated till last friday (Reload happend every sunday night). if the reload date is 24.07.2016 and the data will be loaded for the dates from 17.07.2016 to 22.07.2016. (Next reload will be on 31.07.2016 and the data will be popluated from 25.07.2016 to 29.07.2016.) Sometimes the reload might fails and the date (Random date) will be missing in the dashboard. In this case 20.07.2016 (Sometimes it can be two dates). I just want to show the date in textbox and have to warn the users. I am not suppossed to the touch back end script. I have to do this in front end. If you have any idea, please share.

LOAD * INLINE [

Date

7/17/2016

7/18/2016

7/20/2016

7/21/2016

7/22/2016

];

You can see that the date 7/19/2016 is missng in the above load.

Thanks in advance.

Re: Find Missing Date From Last Week

sunny_talwar — Wed, 27 Jul 2016 02:43:38 GMT

May be try this:

=Concat(Date(If(Index($(=Concat(Distinct Num(Date))), $(=Min(Date)) + ValueLoop(1, 5)), Null(), $(=Min(Date)) + ValueLoop(1, 5))), ',')

Going to be testing this with two dates now.

Re: Find Missing Date From Last Week

sunny_talwar — Wed, 27 Jul 2016 02:44:47 GMT

Seems to be working:

Re: Find Missing Date From Last Week

Wed, 27 Jul 2016 02:45:12 GMT

maybe

=DATE(((min(Date#(Date,'MM/DD/YYYY'))*6)+15)-SUM(Date#(Date,'MM/DD/YYYY')))

Re: Find Missing Date From Last Week

tamilarasu — Wed, 27 Jul 2016 02:53:32 GMT

Thanks Sunny. It's working as expected. Have a nice day.

Re: Find Missing Date From Last Week

sunny_talwar — Wed, 27 Jul 2016 02:54:54 GMT

Awesome. I am going to have a good night now , you have a great day ahead.

Best,

Sunny

Re: Find Missing Date From Last Week

tamilarasu — Wed, 27 Jul 2016 02:59:22 GMT

Hi Rodrigo,

I am getting 16/07/2016 as missing date which is not correct. Sunny's solution is working as expected. Thanks for your help though.

Re: Find Missing Date From Last Week

qliksus — Wed, 27 Jul 2016 04:29:28 GMT

Maybe something like this

concat({<Weeks-={'Sun'}>}aggr( if( Above(Date)<> date(Date-1) , date(Date-1) ) ,Date),',' )