Textcount variable with if statements, count a text value with asterix

[#1]

Hi,
Using textcount and a "if" case where if a) and b) is fulfilled i want it to count a field.
This seems to work, but my problem is when i want it to count all values EXCEPT or "seperated from"...

Ie. this works:
=textCount(if([Lägesstatus]='Sen, ej aviserad' AND [MC]='MCJPN', Lägesstatus,0))

Want to do but doenst work:
=textCount(if([Lägesstatus]='Sen, ej aviserad' AND [MC]<>'MC%', Lägesstatus,0))

I guess my question is two:
a) shouldnt I be able to use % in this case?
b) Can i use the "<>" like this, or is it another command i am looking for?

Any hints on how to solve this?
Kind Regards,

[#2 · Accepted solution]

I couldn't figure out how to do that conditional. I couldn't even do the positive [MC] = 'MC%'.

A workaround: =textCount(if([Lägesstatus]='Sen, ej aviserad' AND WILDMATCH([MC], 'MC*') = 0, Lägesstatus,0))

That should get what you want. If WILDMATCH is 0, that means [MC] does not match the string 'MC*'.

[#3]

Thanks for your quick reply NMiller!
It appears to be working, yet it raises a new issue for me because I must truly missunderstand the "textcount" command.

- - -
a) Imagine you had three tables, loaded from different scripts, with primary keys that bind them. And they might look something like this:
Table1: Partnumber, Partdescription, Responsible, Status1
Table2: Partnumber, Partdescription, Responsible, Status2
Table3: Partnumber, Partdescription, Responsible, Status3
Primary keys being all except for status1-2-3.

b) Then you want to count by responsible and per table according to the formula above using textcount with the "if" on Status1 or 2 or 3 and Responsible .

c) The problem I get is that it counts the results from all three tables, not just the ones that meet the critera ie: [status1]='Example' because the primary keys i guess appears in more tables at once.

In reality I would like three tables completley seperated from each other, so that you could count the results in table 1 then table 2 then table 3 independently - and all at the same time in the same report.

Iam using text objects, if it matters, to do the expressions in. One for each responsible person.

- - -
Hmm - sorry, its really difficult it appears to make any sense about all of this. And I cant attach an example file currently. I guess that means its time for me to hit the books!

[#4]

Yes, I'm not sure exactly where the problem is coming in. It seems like you should be able to do what you are trying to do.

If I am reading it correctly, you want to count the number of items appearing in Table 1 i.e. the number of items with a Status1. I'm guessing once all the tables are linked, the Status2s and Status3s would be blank for an item only appearing in Table 1. Therefore, you should be able to count based on Status1 not null.

I think Set Analysis may be able to help here as that should allow you to easily filter out anything that doesn't have a Status1. I've attached a sample app. This one uses a data structure similar to what you described above. I've used Set Analysis to count specific statuses and counted statuses from only one table. I think these same ideas should be applicable in your case.

[#5]

Yes, you are correct about what you are saying!
The values in Status1, Status2 or Status3 are not the same however. Just in case it was unclear.

If you can produce a example of set analysis that would be very much appreciated. I took a look but I guess my frustration wont let me get it ATM... maybe some help would do it.

Also,
Attaching some sample jpegs. Using personal edition 9, so i guess i cant share any files.
In the example below i get the results of "2", yet i only have 1 row that matches my criteria as also seen on the picture. So where does the other "1" come from i wonder...

With current selection I actually do get a hit on another partnumber in another sheet (picture 3 below) - where the component number matches up - but it doesnt match up my criteria so why would it count it - same as you are saying.

<Deleted image, sorry>

the three tables
<Deleted image, sorry>

this is the other table, and as shown it gets two hit with the selecton from above:
<Deleted image, sorry>

Hope it make any sense at all... you seem to be on track though.

[#6]

The Set Analysis is kind of equivilent to your If statement. Set Analysis is usually faster and more effecient. The expression in your first image could be rewritten in Set Analysis as: =textCount({<anmodanStatus = {'Annulering'}, MC = {'MCHPA'}>} anmodanStatus)

I doubt this will fix your problem though, because it should resolve to the same answer as your if statement. It's worth a try though.

Have you tried the same formula using regular Count or even try "=Sum({<anmodanStatus = {'Annulering'}, MC = {'MCHPA'}>} 1)"

Also, is there any other data in that row. If there are others with values there are they also double what they should be? I could see it being a linking issue. If that is the case, you may want to add a Distinct to your textcount. I think you would need "=textCount({<anmodanStatus = {'Annulering'}, MC = {'MCHPA'}>} DISTINCT anmodanStatus)"

[#7]

Man, thank you so much for staying with me on this one - works great!

Used this formula:
=textCount({<anmodanStatus = {'Annulering'}, MC = {'MCHPA'}>} anmodanStatus)

PS. If possible please edit your last respons, since I verified it - i had to put ' ' around my criteria or I would get an error DS.

[#8]

Thanks for the heads up on my error, it has been corrected. In the documentation, there is one sample where a text entry is used without quotes. Most of the other samples use quotes. I seem to mostly use numeric fields in Set Analysis, so I haven't come across that error yet. It makes sense that the quotes would be required.

I'm glad you got it all working.