Date Picker not properly setting date condition to sheet and data

[Marcushenriquesk]

Currently I am working on a Sheet that contains some kpi's that illustrate amount of products we have sold with a date picker object to allow the user to select a date range that needs focus. In the auto generated section of the data load editor I set the field 'Receive_Date' which is under the format of MM/DD/YYYY Hh:mm:ss to be loaded with this format:

DATE([RECEIVED_DATE],'MM/DD/YYYY') as [RECEIVED_DATE].

and then at the bottom used 'tag field [RECEIVED_DATE] with $date;' so that the data picker can distinguish 'Received_Date' as a eligible field. 

How ever I am noticing a few issues. 

Firstly, when I select 'yesterday' or 'today' which are the predefined fields I can successful create a sheet wise condition for those specific received dates how ever kpi is showing zero which is completely false. I did a check on this by setting the kpi data function to show that specific date without the use of data picker and got a count > 0.

Secondly, whenever I set it to 'last 7 days' or 'last month' the date picker doesn't work, i will click on these functions and the sheet with refresh and the date picker will only show something like the last 3 days

I.E.  Ill select last 7 days and the sheet condition will show 8/20/2021 -8/23/2021. 

 

  

[Marcushenriquesk]

here is an example of showing that we have multiple 'acc_ids' with received date 8/22/2021. but when the date picker is set to that date there is no data.

[marcus_sommer]

Your [RECEIVED_DATE] isn't a date else a timestamp in the format of a date. To make a date from it you need to cut the time-part, for example with:

DATE(floor([RECEIVED_DATE]),'MM/DD/YYYY') as [RECEIVED_DATE]

- Marcus

[Marcushenriquesk]

okay I can try this. however, what if i want to see the time part again. wont that perm change the field on the entire sheet?

[marcus_sommer]

If you really need the time-part I suggest to load this field twice - one time as timestamp and once as a real date and applying/calling the right field in the needed context.

- Marcus