Help with Set analysis syntax

[#1]

Hi,

I have a set analysis expression that used to work, but suddenly it doesn't (well, it does in one application but not in a later version of the same application), which leads me to think that I've done something wrong in the syntax.

What I want to do is to calculate the number of suppliers (UniqueCountSupplierNumber) that only exists once. I also only want to take the current month into consideration (I have a variable for that set anaylis).

count( {< $(vCurrentMonthSet), UniqueCountSupplierNumber = {"=count( {1<$(vCurrentMonthSet)>} UniqueCountSupplierNumber)=1"} >} UniqueCountSupplierNumber )

Any thoughts on how I should change the expression or how I could reach the same result in another way?

Regards, Anders

[sunny_talwar]

I don't see anything wrong with the expression. Would you be able to share a sample to see what might not be working?

[Colin-Albert]

What is the expression for vCurrentMonthSet and  what value does the is the variable set to?

[Colin-Albert]

Can you add the expression to a straight table chart with a blank column label, then you can see how the set expression is being expanded by examining the column label when viewing the chart

[#5]

Hi,

I took away the variable for the month and did an explicit search instead. It makes it simpler to undersand, but unfortunately it still does not work...

=count( {< SpendMonth#={'44'}, UniqueCountSupplierNumber = {"=count( {1< SpendMonth#={'44'}>} UniqueCountSupplierNumber)=1"} >} UniqueCountSupplierNumber )

[#6]

Unfortunately not. It's part of a large application that I cannot share. And the strange thing is that the expression works in an older version, but not in the current one...

[Anil_Babu_Samineni]

count( {$< "$(vCurrentMonthSet)", UniqueCountSupplierNumber = {"=count( {1<"$(vCurrentMonthSet)">} UniqueCountSupplierNumber)=1"} >} UniqueCountSupplierNumber )

Or else reply to colin

[Colin-Albert]

If you view your expression in a straight table chart with a blank label, what does the label show for the

expression field?

=count( {< SpendMonth#={'44'}, UniqueCountSupplierNumber = {"=count( {1< SpendMonth#={'44'}>} UniqueCountSupplierNumber)=1"} >} UniqueCountSupplierNumber )

You may need to change the header row to show several rows and make the column wider to see this.

Does the UniqueCountSupplierNumber field resolve to valid value?

Can you send a screen-shot of the column header?

[#9]

Hi,

I started to put that together but then I noticed something really strange that I think is the culprit behind it all. It doesn't seem to be a set analysis problem after all.

I have two fields - one called UniqueCountSupplierNumber and the other UniqueSupplierNumber. The first one is just an Autonumber of the latter one and created and stored as a field in the data model in just as many instances as the original one. However, when I do a count of the two, one show the full count and the other (the autonumber one) does a distinct count.

I've tried some different things. First just looked at the data in a straight table and it seems to look fine.Then I tried to do some normal counts. If you look at the picture below (just a screencopy from the QV), the top left shows the raw data. Then I've added a normal count in the other charts with the only difference that I show a different amount of dimensions or different dimensions. All the counts should show 13, but when I only use the autonumber as the dimension, it does a distinct count of the autonumber one, but not of the original one.

This really boggles my mind. Any ideas???

[#10]

I found the error. It had to do with the count function (see link to post by hic below) . I was counting a key field (not wanting a distinct result) and then the count does not work (when you think of it, it is obvious, but I've never thought of it in the past). I don't know why it worked in the older file (it's a key field there as well), but at least I found the error.

And thanks for all the help and suggestions!

Count or Count distinct?