topic Re: Help with Set analysis syntax in QlikView

Help with Set analysis syntax

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help with Set analysis syntax

sunny_talwar — Thu, 18 Aug 2016 08:29:38 GMT

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

Re: Help with Set analysis syntax

Colin-Albert — Thu, 18 Aug 2016 08:40:25 GMT

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

Re: Help with Set analysis syntax

Colin-Albert — Thu, 18 Aug 2016 08:42:08 GMT

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

Re: Help with Set analysis syntax

Thu, 18 Aug 2016 08:56:39 GMT

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 )

Re: Help with Set analysis syntax

Thu, 18 Aug 2016 08:57:43 GMT

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...

Re: Help with Set analysis syntax

Anil_Babu_Samineni — Thu, 18 Aug 2016 09:00:51 GMT

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

Or else reply to colin

Re: Help with Set analysis syntax

Colin-Albert — Thu, 18 Aug 2016 11:21:58 GMT

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?

Re: Help with Set analysis syntax

Thu, 18 Aug 2016 15:44:43 GMT

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???

Re: Help with Set analysis syntax

Fri, 19 Aug 2016 08:16:23 GMT

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?

Re: Help with Set analysis syntax

hic — Fri, 19 Aug 2016 08:26:23 GMT

The behaviour of Count() when a key field is used as argument has changed over the years. So, that it worked in some versions but not in others is not strange.

My view is that Count(<Key>) ought to always return NULL. It is just not well-defined.

Count(distinct >Key>) should however work, as well as Count(<CopyOfKey>)

HIC

Re: Help with Set analysis syntax

Fri, 19 Aug 2016 08:32:03 GMT

OK, that explains it.

And I can only agree with you regarding returning NULL - would have saved me two days work... I made a copy of the field and now it works as charm