Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jlampard40
Contributor III
Contributor III

Set Analysis to count all less than 14

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

Labels (1)
5 Replies
Anil_Babu_Samineni

Perhaps this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jlampard40
Contributor III
Contributor III
Author

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)

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jlampard40
Contributor III
Contributor III
Author

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.