topic Re: Distinct field in Qlikview in QlikView

Distinct field in Qlikview

gdham123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Distinct field in Qlikview

marcus_sommer — Tue, 29 May 2018 10:41:47 GMT

You could try it in this way:

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

- Marcus

Re: Distinct field in Qlikview

sunny_talwar — Tue, 29 May 2018 11:15:44 GMT

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?

Re: Distinct field in Qlikview

gdham123 — Fri, 01 Jun 2018 10:24:41 GMT

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

Re: Distinct field in Qlikview

marcus_sommer — Sat, 02 Jun 2018 06:37:12 GMT

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

Re: Distinct field in Qlikview

rwunderlich — Sun, 03 Jun 2018 16:05:14 GMT

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

Re: Distinct field in Qlikview

gdham123 — Sun, 03 Jun 2018 17:43:06 GMT

Thanks Rob. Duly noted.

Gary

Re: Distinct field in Qlikview

gdham123 — Sun, 03 Jun 2018 17:45:32 GMT

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

Gary

Re: Distinct field in Qlikview

marcus_sommer — Tue, 05 Jun 2018 12:31:47 GMT

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

Re: Distinct field in Qlikview

gdham123 — Thu, 07 Jun 2018 18:52:42 GMT

Ok, thanks for the explanation Marcus.

Gary