Set Analysis to count all less than 14

[jlampard40]

I am trying the following set analysis with no lucK

=Count({<[WBR_Stat_Date] = {"$(=Date(Today()))"}, {[WBR_Event_Date]-[WBR_Clock_Reset_Date]={<'14'}>} [Event_Key])

I need to count all clients who have been waiting over 14 days, based on the calc [WBR_Event_Date]-[WBR_Clock_Reset_Date] (as above).  I also need it for just the current stat date - hence the field [WBR_Stat_Date] which equals today().

Any help much appreciated as always.  Kind regards

[Anil_Babu_Samineni]

Perhaps this?

=Count({<[WBR_Stat_Date] = {"$(=Date(Today()))"}, [Key]={"=([WBR_Event_Date]-[WBR_Clock_Reset_Date])<14"}>} [Event_Key])

[jlampard40]

Thanks Anil - but not sure what the [Key] field relates to?  Can you advise?

[jonathandienst]

The LHS of a set expression needs to be field name, not an expression. The simplest and most intuitive way to resolve your issue is to create an additional derived field during the load:

LOAD  ...
WBR_Event_Date - WBR_Clock_Reset_Date as Elapsed,
...

And then use this field in the expression:

=Count({<WBR_Stat_Date = {"$(=Date(Today()))"}, Elapsed = {"<14"}>} Event_Key)

[jlampard40]

That works really well actually!

How would I now calculate the % of those waiting <14 days against the total?  I have this so far but I can't seem to convert it into an actual percentage, it's just coming back with decimal - currently 0.10572 but should be represented as 10.572% (I'm displaying this in a text box by the way):

=Num(Count({<WBR_Stat_Date = {"$(=Date(Today()))"}, Elapsed = {"<=14"}>} [WBR_Stat_Date]),'#,###.')/
Num(Count({<WBR_Stat_Date = {"$(=Date(Today()))"}>} [WBR_Stat_Date]),'#,###%.')

Many thanks!

[Brett_Bleess]

James, below are some Set Analysis blog posts that may help you sort things on your own along with Help link just so you can check things out against documentation too.  I am also posting the base URL for the Design Blog area so you can search yourself too, there are quite a few posts on Set Analysis related issues, and I hate to post all of them! 🙂

I did just realize though that it appears your issue is not with Set Analysis at this point, it appears you just need to multiply by 100 to adjust the number properly, but I am not exactly sure where to slip that into the expression, but I think that should do the trick.  

Here is the base Design Blog URL for future reference, should come in handy, lots of good examples and explanations on the more difficult topics...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Oh, almost forgot, do not forget to use the Accept as Solution button to give credit for the right answer too, folks appreciate when you do that for them, and if you get a better answer on the secondary part, you can mark more than one post...

Cheers,
Brett

Regards,
Brett