topic Re: Scenario Help. in QlikView

Scenario Help.

abhaysingh — Wed, 18 Nov 2015 07:18:11 GMT

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

Re: Scenario Help.

tamilarasu — Wed, 18 Nov 2015 07:42:15 GMT

Hi abhay,

Try something like,

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

IF(DateSubmitted > DateDue , 'Submitted Late',

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

Let me know,

Re: Scenario Help.

abhaysingh — Wed, 18 Nov 2015 07:56:21 GMT

thanks for revert, This is some logic is working...

i want below

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

thanks

Re: Scenario Help.

vincent_ardiet — Wed, 18 Nov 2015 07:56:54 GMT

Hi Abhay,

Can you explain us your datamodel or give a screenshot of it?

You issue is maybe link to the way your data are organized.

Regards

Vincent

Re: Scenario Help.

tamilarasu — Wed, 18 Nov 2015 08:02:16 GMT

You are using DateSubmitted as your master calendar field. If DateSubmitted field is blank, you can not see the Overdue when you click any year. Do you have any blank cells in master calendar? Could you post your application.?

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

thanks

Also, you have mentioned that the overdue should display for any year. I don't understand the concept here. Please elaborate.

Re: Scenario Help.

abhaysingh — Wed, 18 Nov 2015 08:07:51 GMT

Hi Vincent,

this is only one table.. no other table in data model

i Tamil Nagaraj,

yes right and concept is in Datesubmit there are null dates as well, or if i m not selecting any date than as on overdue task are showing.. but i want when i select any calendar year created from datesubmitted overdue also come..

Sorry i can't post the Sample App.

Thanks

Re: Scenario Help.

Mark_Little — Wed, 18 Nov 2015 08:23:29 GMT

HI,

Maybe, add today as date submitted for the one that don't have a date, but after you apply the above logic, use a proceeding load.

Then you can use your TaskStatus flag to exclude the overdue one when you don't want to see them?

Mark

Re: Scenario Help.

tamilarasu — Wed, 18 Nov 2015 08:26:53 GMT

Then you need to create a dummy date for the blank cells. But I don't think this is a good idea. You need to modify your data model and need logic.

Re: Scenario Help.

vincent_ardiet — Wed, 18 Nov 2015 08:39:38 GMT

How do you use your expression?

In a straight table?

What are the dimensions you are using?

Re: Scenario Help.

Anonymous — Wed, 18 Nov 2015 08:56:22 GMT

can we try sth like:

=count({<Status={'Overdue'}, Year={'=$(=year(DueDate))'>} SurveytaskID) //Correct syntax if required

Re: Scenario Help.

vincent_ardiet — Wed, 18 Nov 2015 14:11:16 GMT

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];

Re: Scenario Help.

abhaysingh — Tue, 24 Nov 2015 05:59:39 GMT

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?

Re: Scenario Help.

vincent_ardiet — Tue, 24 Nov 2015 07:42:27 GMT

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.

Re: Scenario Help.

abhaysingh — Tue, 24 Nov 2015 08:16:15 GMT

How can u tell me pls?

Re: Scenario Help.

vincent_ardiet — Tue, 24 Nov 2015 08:23:06 GMT

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