Set Analysis Not Working

[#1]

The following set analysis statement I am trying to use is not working.  Any ideas?

=Count({$<Skill={'Pro'},Role={'to apply'}>} If(Today() - Max(DateOfIssue) <= 30, Name))

Thanks much!

[kiranmanoharrode]

Hi MavRinder,

Declare two Variables:

vToday = Today();

vMaxDateIssue = Max(DateOfIssue);

=Count({$<Skill={"Pro"},Role={"to apply"},30>={"$(vToday)"-"$(vMaxDateIssue)"}>}Name))

Regards,

Kiran

[Anonymous]

you can try like:

=If((Today() - Max(DateOfIssue)) <= 30,Count({$<Skill={'Pro'},Role={'to apply'}>} Name))

[#4]

Hi Kiran,

I tried this but unfortunately it gives an error at the curly bracket ({) after the 30>=

=Count({$<Skill={"Pro"},Role={"to apply"},30>={"$(vToday)"-"$(vMaxDateIssue)"}>}Name))

Regards,

[#5]

Hi

I used your formula and am getting the wrong figure.  Just to explain, I count 140 people at 'Pro' skill level, this should reduce when wanting to count the number of people more than 30 days outside the DateOfIssue. but the formula returns 140, therefore the date calculation isn't working as I want it.

Could you assist?

[Anonymous]

I guess the problem is on today() and Max(DateOfIssue) format, are they in the same format?

If so, the difference between those two is a integer number?

[tamilarasu]

Hi Mavrinder,

Try below,

=If( (Today() - Date(Max(DateOfIssue),'DD-MM-YYYY') )<= 30,

      Count({$<Skill={'Pro'},Role={'to apply'}>} Name)

   )

If your DateOfIssue field format is like "DD-MM-YYYY hh:mm:ss tt", then you need to try below expression,

=If( (Today() - Max(Date(Date#(DateOfIssue,'DD-MM-YYYY hh:mm:ss tt'),'DD-MM-YYYY') ) )<= 30,

      Count({$<Skill={'Pro'},Role={'to apply'}>} Name)

   )

Or simply create a variable

Variable Name = vMax

Definition:  =Max(Date(Date#(DateOfIssue,'DD-MM-YYYY hh:mm:ss tt'),'DD-MM-YYYY'))

Then try below expression,

=If( (Today() - '$(vMax)' ) <= 30,

      Count({$<Skill={'Pro'},Role={'to apply'}>} Name)

   )

[jagan]

HI,

Try like this

=Sum({$<Skill={'Pro'},Role={'to apply'}>} Aggr(If(Today() - Max({$<Skill={'Pro'},Role={'to apply'}>} DateOfIssue) <= 30, 1, 0), Name))

Hope this helps you.

Regards,

Jagan.