The sum of the values for one year, if it also meets the conditions in another year

[Jannara]

Hi,

I'm trying to sum values for the year 2021 but only it the conditions are also met in 2022.

The short example of dataset is below:

Customer	Product	City	Name	Qty	Year	Sale
Astrida	AA	xxx	abc	1	2021	2
Betacab	BB	yyy	cde	1	2021	3
Canutility	AA	xxx	efg	0	2021	4
Astrida	AA	xxx	abc	1	2022	5
Betacab	BB	yyy	cde	2	2022	6
Canutility	AA	zzz	efg	1	2022	7
Astrida	AA	xxx	cde	1	2021	8

I want to create the pivot table with only customer and product that shows the 2021 sum and 2022 sum of products that have Qty=0 or Qty=1 for both, 2021 and 2022. So the result should be as follow:

Customer	Product	Sale 2021	Sale 2022
Astrida	AA	10	5
Betacab	BB	0	0
Canutility	AA	4	7

I tried various ways (some examples below), but always qlik ignore this rule for the 2022 and as a result I see the value also for BB product in 2021

sum({<"=max(qty)<2">-< year={"$(=max(year))"}>} sale)

Sum(
  {<[customer&product&city&name] = {"=Count(DISTINCT {$< [qty] = {'0', '1'}>} year) = Count(DISTINCT {$} year) "}>}
  {<year={"$(=Min({$} year))"}, [qty]={"0", "1"}>} sale
)

Can you please suggest on this?

[E_Røse · Accepted solution]

Try this measure

if(max(total<Product>Qty)<2, sum(Sale),0) in the pivot-table.

You might want to add a set within  the max-function, depending on what you want to happen when you make selections.. fe.g.

if(max({1} total<Product>Qty)<2, sum(Sale),0).

Please like and mark my answer as a solution, if it resolved your issue.

[Jannara]

Thank you!!! This was really helpful. I slighty modified your formula and added sum(aggr ... ) at the beginning because although it showed the correct values for the details, it showed 0 for the totals in my pivot table and now it is perfect.