topic Re: Problem AGGR Nodistinct in App Development

Problem AGGR Nodistinct

tcc_pascal1 — Wed, 13 Mar 2024 16:18:17 GMT

Hi all,

I have the problem of adding up repeated values in the aggregation in a unique way

Monat	Kat 1	Kat 2	Kat 3	Product ID	Value
Jan	A	C	F	1	22
Jan	A	C	G	1	22
Jan	A	D	F	1	22
Jan	A	D	G	1	22
Jan	A	C	G	2	25
Jan	B	C	F	2	25
Jan	A	C	G	2	25
Jan	B	C	F	2	25
Feb	A	C	F	1	18
Feb	A	C	G	1	18
Feb	A	D	F	1	18
Feb	A	D	G	1	18
Feb	A	C	G	2	15
Feb	B	C	F	2	15
Feb	A	C	G	2	15
Feb	B	C	F	2	15

Because a product can be in different categories, one product is linked with one value multiple times. For a dashboard I would like to show the value for all combinations of categories 1, 2 & 3 for the required dimensions, but suppress unnecessary double counting.

In my example a result is:

Monat	Kat 1	Value
Jan	A	47
Jan	B	25

The function I have tried is: SUM(AGGR(Nodistinct MIN(Value),[Product ID]))

Can you help me with the correct formula?

Re: Problem AGGR Nodistinct

Digvijay_Singh — Wed, 13 Mar 2024 17:48:17 GMT

Little confusing but can you share expected output, expected results? In your final chart you need only Monat,Kat1 and Value?

Re: Problem AGGR Nodistinct

edwin — Wed, 13 Mar 2024 18:48:12 GMT

looking at the profile of your data, it appears a product can only have 1 value per month, a product can have different categories per month. my suggested solution is to refine your data model. this will result in a simpler expression that will be easy to maintain. i suggest you break this up into 2 tables: categories and product value:
the link is by month and product (as the characteristics of your product change by month)

this is the script i used:

Categories: Load Monat & '|' & [Product ID] as key, [Kat 1], [Kat 2], [Kat 3] Inline [ Monat,Kat 1,Kat 2,Kat 3,Product ID Jan,A,C,F,1 Jan,A,C,G,1 Jan,A,D,F,1 Jan,A,D,G,1 Jan,A,C,G,2 Jan,B,C,F,2 Feb,A,C,F,1 Feb,A,C,G,1 Feb,A,D,F,1 Feb,A,D,G,1 Feb,A,C,G,2 Feb,B,C,F,2 ]; Products: load Monat & '|' & [Product ID] as key, Monat, [Product ID], Value Inline [ Monat,Product ID,Value Jan,1,22 Jan,2,25 Feb,1,18 Feb,2,15 ];

and this is the resulting table:

as you can see the expression is a simple sum(Value)

you havent really explained all the bsuiness rules as your data has duplicates and i suspect there must be some requirement why you have that but as far as the stated requirement, you are with this solution able to sum the values of each product per month correctly

Re: Problem AGGR Nodistinct

tcc_pascal1 — Thu, 14 Mar 2024 08:18:29 GMT

Thanks for your replies!

Expected results in my example are:

Monat	Kat 1	Kat 2	Value
Jan	A	C	47
Jan	A	D	22

and:

Monat	Kat 3	Value
Jan	F	47
Feb	F	33

Think of it as a sweater that sold for €300 in March, for example. This sweater has the characteristics Men, Business and Winter in category 1. In category 2, the sizes are M, L and XL. There is also a T-shirt with a retail value of €100 and category 1 Men, Leisure and Summer and category 2 S and M.
As a result I want to see an overall of 400€ in March, for size S it is 100€, and most import thing:

Monat	Kat 1	Value
March	Men	400 €
March	Business	300 €
March	Winter	300 €
March	Leisure	100 €
March	Summer	100 €

So multiple counting is fine, if a dimension with multiple characteristics is visible, but in total the value is still the sum of products values 300€ + 100€ = 400€

Re: Problem AGGR Nodistinct

tcc_pascal1 — Thu, 14 Mar 2024 08:32:39 GMT

This looks like a really smart solution, but I would like to make it work with one table, because we prepare the data in a data warehouse beforehand

Re: Problem AGGR Nodistinct

edwin — Thu, 14 Mar 2024 13:35:46 GMT

Qlik has an ETL-like capability in its load script. you shouldnt confine yourself with what form your data is available in. you model your data the way you think it should be or what is best for your dashboard. at the end of the day, you will decide which works best for you. im sure there will be a smart complicated expression that will help you arrive at the required output but keeping things simple helps in the long run. if you have a single table, you can break it up into two. i didnt add that in as it was obvious - you have 1 table and to deliver your requirement i recommend a DM change so you can do something like:

NoConcatenate Categories: Load Monat & '|' & [Product ID] as key, [Kat 1], [Kat 2], [Kat 3] Resident YOURTABLE; Products: load distinct Monat & '|' & [Product ID] as key, Monat, [Product ID], Value resident YOURTABLE; drop table YOURTABLE;

i didnt add a distinct in the Categories table as your data had duplicates which may drive other requirements not defined above. however, looking at the data in isolation makes me think the duplicates dont make sense.

sample run:

Re: Problem AGGR Nodistinct

tcc_pascal1 — Mon, 18 Mar 2024 12:39:31 GMT

I have implemented your solution and it works perfect. Thanks a lot!

Re: Problem AGGR Nodistinct

edwin — Fri, 22 Mar 2024 13:40:49 GMT