Functions and fields

[Ari_B]

I am trying to make a KPI chart that contains a specific response within a field.  I am using an 'if' function which is going well, however, I want to have the measure in the KPI be the 'count' of another field (one that is not numerical, hence the 'count').  

This is basically what I have: 

=If(FIELD_THAT_IS_NOT_NUMERIC='Yes' or 'No', count(COUNT_OF_THIS_FIELD_IS_THE_NUMBER_OF_RESPONSES_IN_MY_DATA_SET))

This is not working.  It seems to just be showing only when 'Yes' or 'No' were not the response (basically the times nothing was put in).  This, which should be similar, is working:

=If(FIELD_THAT_IS_NOT_NUMERIC='Certain response',[THE_DATE.autoCalendar.Year]) 

What is going wrong here?  Would 'count' simply not work in this scenario?  Is there a more efficient way to do 'if' statements?  Is this just KPIs?  Is it the 'or'?  I am just pretty lost on this front.  

[GaryGiles]

Not sure if this is the issue you are having, but looking at your if statement, one thing that stands out is that you have to specify the fieldname= after the "or".  

=If(FIELD_THAT_IS_NOT_NUMERIC='Yes' or FIELD_THAT_IS_NOT_NUMERIC='No', count(COUNT_OF_THIS_FIELD_IS_THE_NUMBER_OF_RESPONSES_IN_MY_DATA_SET))

[Saravanan_Desingh]

You can also try using the IF inside COUNT

Count(If(Match(FIELD_THAT_IS_NOT_NUMERIC,'Yes','No'),COUNT_OF_THIS_FIELD_IS_THE_NUMBER_OF_RESPONSES_IN_MY_DATA_SET))

[PrashantSangle · Accepted solution]

Hi,

Your query is not clear, can you explain with some data. Be more specific about fields. try with set analysis also like

Count({<FIELD_THAT_IS_NOT_NUMERIC={"Yes","No"}>}fieldName)

Regards,

[Ari_B]

Sure!  Sorry about that!  

The actual data is regarding whether or not a specific party was declared 'at fault' for an incident.  I wanted a KPI that only showed the number of incidents where that specific party was declared 'at fault'. 

#An issue was that much of the responses recorded in this category were neither 'Yes' nor 'No' and were left blank or inconclusive in some way (we are aware this is not the best data in terms of consistency but it's what we have) so we wanted a way to display only those values where it was 'yes' (or 'yes' or 'no' for a multi KPI). < Still true BUT, we realized that the data responses we had were NOT actually listed as 'Yes' and 'No' (we have a key that listed the data as such but it was not accurate) but as something else.  

So we went in and changed the names of the responses and made the blank responses null and then this function you provided seems to be working well!  However!  The comma between the 'yes' and the 'no' in your function does not seem to be combining them or making them either/or, but is doing something else.  (The 'Yes' and 'No' combined response would be 88.23k, the response with the comma would be 46.82k, and the response of just 'Yes' was 44.47k.  Which is a bit odd).  But regardless, the single response works well!

So thank you very much!  Do you have any particular explanation as to why this works or why the 46.82k occurs so that I may better be able to write my own functions in the future?  

[Ari_B]

Some of my data was labeled incorrectly in the first place so that explains why some things were not working.  I got rid of some null values, just getting the data cleaner in general, and this ran smoothly when I ran it with just what would be the 'yes' response.  

However, this number is not equivalent to the number I got with some other methods.  Another reply provided me with another method, which also works well, but has a different number.  I also made a multi KPI that had the numbers of both options (what would be 'yes' and 'no' separately) and the 'yes' number matched the number from the other method.  

All the 'yes' response numbers I have gotten:

Your method: 45.41k

Other method: 44.47k

Number from multi KPI: 44k 

It is reasonable to suggest that the 44k number is just 44.47k rounded but 45.41k cannot round to 44k.  

My point in telling you this is not to be rude or anything but to ask about the 'Match' function.  It is the only different function between your replies.  So what does 'Match' do in a function?  I really appreciate your reply to my original question though so thank you!

[Ari_B]

Sad to say but this method did not work.  It may just be how my data is (I was not very clear on that I'm afraid) but yeah, it just came back with a KPI with nothing but a little '-' in it.  

I also did have an issue with data organizing and labeling so it cleared up many of my issues once I made some responses null and renamed a few things.  

Thank you for your help though!