topic Set Analysis Not Working in QlikView

Set Analysis Not Working

Thu, 26 Nov 2015 13:05:15 GMT

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

kiranmanoharrode — Thu, 26 Nov 2015 13:24:34 GMT

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

Anonymous — Thu, 26 Nov 2015 13:33:04 GMT

you can try like:

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

Re: Set Analysis Not Working

Thu, 26 Nov 2015 14:09:51 GMT

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

Thu, 26 Nov 2015 14:16:41 GMT

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

Anonymous — Thu, 26 Nov 2015 16:30:53 GMT

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

tamilarasu — Fri, 27 Nov 2015 01:43:33 GMT

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)

)

Re: Set Analysis Not Working

jagan — Fri, 27 Nov 2015 02:11:54 GMT

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.