Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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!

7 Replies
kiranmanoharrode
Contributor III

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

balrajahlawat
Esteemed Contributor

Re: Set Analysis Not Working

you can try like:

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

Not applicable

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,

Not applicable

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?

kawamuram
Contributor II

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)

   )

MVP
MVP

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.

Community Browser