7 Replies Latest reply: Nov 26, 2015 9:11 PM by jagan mohan rao appala

Set Analysis Not Working

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!

• Re: Set Analysis Not Working

Hi MavRinder,

Declare two Variables:

vToday = Today();

vMaxDateIssue = Max(DateOfIssue);

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

Regards,

Kiran

• Re: Set Analysis Not Working

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,

• Re: Set Analysis Not Working

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.

• Re: Set Analysis Not Working

you can try like:

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

• Re: Set Analysis Not Working

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?

• Re: Set Analysis Not Working

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?

• Re: Set Analysis Not Working

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)

)