Distinct field in Qlikview

[gdham123]

Hi,

I am trying to sum a field based on conditions in two other fields.

So I have Name field which has to contain "arch", and the Area_ID has to be distinct. I would then like to sum the Orders. I have tried various equations, such as:

Num(Sum({$<Name={'*arch*'},Count(Distinct Area_ID>{0}>} Orders),'#,##0')

The Area_ID >0 was just something I added so as to be able to use Distinct, as it requires an aggregate function (in this case Count).

Any help would be greatly appreciated.

Thanks in advance.

[marcus_sommer]

You could try it in this way:

Sum({$<Name={'*arch*'}, Area_ID = {'*'}>} Orders)

- Marcus

[sunny_talwar]

If what Marcus provided doesn't work for you, would you be able to share few rows of dummy data and explain as to what exactly are you looking to get?

[gdham123]

Thanks for your answer Marcus. However, each area still appears many times.

Each name appears in an area several times, so a many to many relationship. I am simply trying to get a sample from each area (just one), dynamically to match the dashboard selection.

A QlikView technician has suggested:

Num(Sum(Distinct{$<Name={'*arch*'},Orders={'>=1'}>} Area_ID),'#,##0')

I have summed the orders separately, to match the dimension.

This seems to be working ok.

Thanks for your assistance.

Gary

[marcus_sommer]

A sum(distinct VALUE) is quite uncommon to get a correct result. Therefore I suggest to check the datamodel to ensure that it is suitable to your requirements.

- Marcus

[rwunderlich]

Note that using single quotes in a wildcard  or advanced search expression:

<Name={'*arch*'},Orders={'>=1'}>

is incorrect. It should be double quotes.

<Name={"*arch*"},Orders={">=1'">

This is enforced in QV12.20 for new documents.  I recommend you code using double quotes as it will work in all releases.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[gdham123]

Thanks Rob. Duly noted.

Gary

[gdham123]

Thanks Marcus. Is there another way to get a unique return of a dimension?

Gary

[marcus_sommer]

It depends on the aim of your calculation. Like above mentioned there are not many use-cases in which a sum(distinct Field) returned the wished results and therefore I assume a not properly build datamodel probably with some duplication of records by any joining-approaches.

A few sample records (only the here relevant fields and without the set analysis conditions stuff - it won't change the main logic/aim of it) and why you need it and which result you expect from it - will be quite helpful to get an idea what do you want to reach.

- Marcus

[gdham123]

Ok, thanks for the explanation Marcus.

Gary