topic Re: Need help on Set Analysis (Count of record between two dates) in QlikView

Need help on Set Analysis (Count of record between two dates)

neha_sri — Wed, 03 Jun 2015 12:13:50 GMT

hi All,

I have a source field with following type of date structure , please see above,

Now I have converted this format to

using following expression

date((MakeDate(Year(date(date#(right([Year Month],2),'YYYY'),'YYYY')),Month(Date(Date#(left([Year Month],3),'MMM'),'MMM')))),'YYYYMMDD')

from this I found min and max date .

And I am trying to find the count of employee between min date and max date .

I have tried many things but it always comes zero.

Please help....

Re: Need help on Set Analysis (Count of record between two dates)

Wed, 03 Jun 2015 12:20:54 GMT

Hi Neha,

Do you have a sample document to look at? However, if you are planing to count every employee between min and max date - will that not mean that you should could all of the employees? Or do you have employees without any date connected to them?

Re: Need help on Set Analysis (Count of record between two dates)

ankit777 — Wed, 03 Jun 2015 12:21:31 GMT

can you provide some sample data?

Re: Need help on Set Analysis (Count of record between two dates)

Anonymous — Wed, 03 Jun 2015 12:25:03 GMT

Hi Neha

you can use below format :

Count({$<Date = {">=$(=Min(Date))<=$(=Max(Date))"}>}EMPLID)

for Min and max date you need to create two variables and use those variables here in Set analysis.

for min and max date : you may need to create two different date column in your model.(So that selection will not over write)

Just attached your sample app that would be helpful.

Thanks

BKC

Re: Need help on Set Analysis (Count of record between two dates)

Anonymous — Wed, 03 Jun 2015 12:27:00 GMT

also try to see the variables in a text box and make sure they are not converting into number (they should be in same format as your original date column)

Re: Need help on Set Analysis (Count of record between two dates)

sunny_talwar — Wed, 03 Jun 2015 12:28:52 GMT

balkumarchandel chandel‌, this might not work as the Min and Max will change the Date into Number format

Count({$<Date = {">=$(=Min(Date))<=$(=Max(Date))"}>}EMPLID)

She might need this:

Count({$<Date = {">=$(=Date(Min(Date), 'YYYYMMDD'))<=$(=Date(Max(Date), 'YYYYMMDD'))"}>}EMPLID) or

Count({$<Date = {"$(='>=' & Date(Min(Date), 'YYYYMMDD') & '<=' & Date(Max(Date), 'YYYYMMDD'))"}>}EMPLID)

Re: Need help on Set Analysis (Count of record between two dates)

Anonymous — Wed, 03 Jun 2015 12:32:43 GMT

That's what I suggested her to use variables not directly min(date) and max date .

Expression was just a sample ..

Re: Need help on Set Analysis (Count of record between two dates)

Anonymous — Wed, 03 Jun 2015 12:35:24 GMT

Neha can you just reduce your data and share a sample application if possible.

Re: Need help on Set Analysis (Count of record between two dates)

sunny_talwar — Wed, 03 Jun 2015 12:36:00 GMT

‌and that is why I liked your second comment

Re: Need help on Set Analysis (Count of record between two dates)

jonathandienst — Wed, 03 Jun 2015 12:36:21 GMT

Firstly, you can simplify your date expression to (in the load script):

Date(Date#([Year Month], 'MMM-YY'), 'YYYYMMDD') As Date,

From your post, it looks like you are calculting this date in the front end. I suggest that you use the expression above in your load script to load the date as a proper QV date value.

You do not give any examples of what you tried, but assuming you created a date field as suggested and that employees can be counted using a field like EmployeeID, you need something like:

=Count({<Date = {">=$(=Date(Min(Date))) <=$(=Date(Max(Date)))"}> EmployeeID})

If nothing is selected, that will count all employees with a non-null Date. If you select some dates, it will count employees from the minimum selected date to the maximum selected date