Scenario Help.

[abhaysingh]

Hi All,

i have one table in which there is SurveyTaskId,DateDue and Datesubmitted field

IF(DateSubmitted <= DateDue,   'Completed on time',

   IF(DateSubmitted > DateDue , 'Submitted Late',

       IF(ISNULL(DateSubmitted) OR LEN(DateSubmitted) = 0, 'OverDue')))  AS [TaskStatus],

right now my master calender is working on Date Submitted, so when i am selecting year, Completing on time and Submitted date are coming but overdue is not bcoz they are nulls i think.. how i can do this?

Ouput, Overdue should Also come when i m selecting any year

Regards

ABy

[vincent_ardiet · Accepted solution]

Ok, I have not seen that you were in the script.

So, you can add a new field DateReported like this:

if(ISNULL(DateSubmitted),DateDue,DateSubmitted) as DateReported

Then you base your calendar on this field instead of DateSubmitted.

And maybe also in your TaskStatus expression start to test if DateSubmitted is NULL first:

IF(ISNULL(DateSubmitted) OR LEN(DateSubmitted) = 0, 'OverDue',
IF(DateSubmitted <= DateDue, 'Completed on time',
IF(DateSubmitted > DateDue , 'Submitted Late')))  AS [TaskStatus];

[abhaysingh]

hi Vincent,

it is working but one issue i am facing is that, due date have dates for year 2020, so now each dates is consolidated so at filter year are showing till 2020 from 2012.. which not making sense.. i have created filter to segregate it for dashboard, but is there any other way as well?

[vincent_ardiet]

Hi,

You can for example in your master calendar create a YearFilter field which is NULL when in the future and use this field in your listbox. So, you will not see the future years but they are still present in the data model.

[abhaysingh]

How can u tell me pls?

[vincent_ardiet]

I don't know the script of you mastercalendar...

First create a variable:

Let curYear=Year(Today());

Then, when you're generating you're calendar in the LOAD statement you will have something like this:

if(Year<=$(curYear),Year) as YearFilter