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

[neha_sri]

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....

[#2]

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?

[ankit777]

can you provide some sample data?

[Anonymous]

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

[Anonymous]

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)

[sunny_talwar]

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)

[Anonymous]

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

Expression was just a sample ..

[Anonymous]

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

[sunny_talwar]

‌and that is why I liked your second comment  

[jonathandienst · Accepted solution]

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