topic Re: Equating the date fields based on current selections in QlikView

Equating the date fields based on current selections

Wed, 15 Jan 2014 11:01:11 GMT

Hi,

I have a requirement wherein I am supposed to display the count of tasks that got submitted and complete in the time frame as per the current selections i.e., in case i have selected the Common year as 2013,I would want to display the tasks that got submitted and also completed in 2013 .Suppose if i select month as jan then the tasks that got submitted and completed during jan irrespective of the year must get displayed.I did try to acheive the same using the below expression

count({<Flag={1},
CompletedYear={$(=GetFieldSelections(CommonYear))},

CompletedMonth={

$(=GetFieldSelections(CommonMonth))},

CompletedWeek={

$(=GetFieldSelections(CommonWeek))},

CompletedDay={

$(=GetFieldSelections(CommonDay))}
>}

TaskId)

But the issue is that incase i do not make selection on any of the above fields like if i do not select day.The expresion does not give me the desired result.So please help me optimize the above expression

Re: Equating the date fields based on current selections

Anonymous — Wed, 15 Jan 2014 18:02:30 GMT

If you don't make selections, the function GetFieldSelections() returns null. If you want this to work without selections, try using concat():

=count({<Flag={1},

CompletedYear={$(=concat(CommonYear,','))},

CompletedMonth={$(=concat(CommonMonth,','))},

CompletedWeek={$(=concat(CommonWeek,','))},

CompletedDay={$(=concat(CommonDay,','))}

TaskId)

Regards,

Michael

Re: Equating the date fields based on current selections

Nicole-Smith — Wed, 15 Jan 2014 18:20:12 GMT

Michael Solomovich's answer should work, but I think the use of P() should also work and it's a bit shorter:

=count({<Flag={1},

CompletedYear=P(CommonYear),

CompletedMonth=P(CommonMonth),

CompletedWeek=P(CommonWeek),

CompletedDay=P(CommonDay)

TaskId)

Re: Equating the date fields based on current selections

Thu, 16 Jan 2014 04:45:47 GMT

Thanks Michael the solution that you have provided is giving me the desired results.Can you please explain the use of concat function here and the logic behind I dont seem to understand the logic used,it would be very helpful if you would guide me on the same

Re: Equating the date fields based on current selections

Thu, 16 Jan 2014 04:48:50 GMT

Hi Nicloe,

The approach you have suggested is working well.I am unable to figure what is P() and the logic behind the code.So could you please explain the same

Re: Equating the date fields based on current selections

brindlogcool — Thu, 16 Jan 2014 04:55:45 GMT

If you are looking for P( ) definition. P( ) refers to set of possible values. You can refer the set Analysis section for more details

Re: Equating the date fields based on current selections

Thu, 16 Jan 2014 05:02:32 GMT

Hi Anushree,

CompletedYear={$(=concat(CommonYear,','))}

here concat function concatenates the values selected on frontend for CommonYear with ',' and returns it to the CompletedYear

If you select 2012 and 2013 then it returns - 2012,2013

CompletedYear=P(CommonYear)

P() function returns all the possible CompletedYear values of selected CommonYear value

Hope you understood.

Regards,

Nuthan

Re: Equating the date fields based on current selections

Nicole-Smith — Thu, 16 Jan 2014 14:01:44 GMT

Anushree,

I think Brindlogcool and nuthan kumar did a good job explaining P(), so you should be able to figure it out from there.

Also, please mark helpful and correct answers in order to help others find solutions to their problems as well

Re: Equating the date fields based on current selections

Mon, 20 Jan 2014 04:07:25 GMT

Thanks Nuthan for a very good explaination