Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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.