topic Re: Help with dates in QlikView

Help with dates

Tue, 27 Jan 2015 15:03:52 GMT

Good morning.

I'm working on a file where I have a chart with two expressions:

= Count ({$ <[Country] = {'Argentina'}>} DISTINCT [Case Number])
and
= Count ({$ <[Country] = {'Argentina'}, [state] = {'Solved'}>} DISTINCT [Case Number])

This graph compares the total registered cases vs total cases solved, the problem is when I selected a date for a control that has the expression = MonthName ([Date Close])because the first expression must be governed by a field called [Date created], and the second by [Date Close].

As I get when a date [Date Close] is selected, also the filter that date is executed, but for [date creation] and with this to see the difference in the open in the month vs the closeness in the month?

Thank you very much for your support.

Re: Help with dates

datanibbler — Tue, 27 Jan 2015 15:10:55 GMT

Hi Jorge,

first of all, you should question yourself: Does the user really need to be able to select on this or that field?

Then, maybe it would be a possible solution to create more date_fields (combined from two different dates?) in the script and then make the expressions independent from this or that (you can make a set_expression disregard all user_selections, or just specific ones)

HTH

Best regards,

DataNibbler

Re: Help with dates

Tue, 27 Jan 2015 15:53:12 GMT

Thank you very much for the prompt reply DataNibbler, I have prepared a model in which I show my need, I show below image:

In the image I show a list of dates and a graph, the need is that the user can select a date and see the capacity of resolution in the month selected for each site.

I appreciate your support.

Best Regards.

Re: Help with dates

Tue, 27 Jan 2015 15:57:11 GMT

Sorry DataNibbler, I was wrong to respond, re sending my answer :

Thank you very much for the prompt reply DataNibbler, I have prepared a model in which I show my need, I show below image:

In the image I show a list of dates and a graph, the need is that the user can select a date and see the capacity of resolution in the month selected for each site.

I appreciate your support.

Best Regards.

Re: Help with dates

datanibbler — Tue, 27 Jan 2015 16:14:13 GMT

I don't quite understand what exactly is your issue here:

- I understand you have two date_fields:

- Date_Created

- Date_Closed

Those shouldn't interfere with each other actually, since a case can neither be closed nor solved before it has been created - so Date_Created is like your Start_Date - it should not rule out any cases (but those before)?

Anyway, you can use set_expressions I think to disregard any one selection_field you want in your expression: For instance

>> count({$<Date_Create = , Country = {"Argentina"}, status = {"closed"} DISTINCT case_nr>} <<

=> That will take into account all user_selections (because of the $), BUT the selection on >Date_Create< will be disregarded for this calculation.

HTH

Best regards,

DataNibbler

Re: Help with dates

Tue, 27 Jan 2015 16:50:44 GMT

Sorry, I'm trying to test the expression on my chart, but I mark error can tell me where I'm wrong:

=count({$<[Date_Create]=, [Country] = {'Argentina'}, Estatus = {'Solved'} DISTINCT [Case_Nr]>}

Thanks for you help.

Regards.

Re: Help with dates

rwunderlich — Tue, 27 Jan 2015 17:18:00 GMT

I think you will want to pull both dates together in a common date. For more info and examples, see:

Canonical Date

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Help with dates

Tue, 27 Jan 2015 17:33:12 GMT

Thank you very much Rob, this is my case.

I implement shared solution.

Thank you very much for all your support.
Regards.