Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You could try it in this way:
Sum({$<Name={'*arch*'}, Area_ID = {'*'}>} Orders)
- Marcus
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?
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
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
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
Thanks Rob. Duly noted.
Gary
Thanks Marcus. Is there another way to get a unique return of a dimension?
Gary
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
Ok, thanks for the explanation Marcus.
Gary