Development over month with only new unique values per month

[Stayhungry]

this forum has helped me a lot and thanks in advance

I am trying to see new unique values over month that has not occurred in previous months, and I am trying to solve it with an expression, I have seen more or less this question when searching but it was unanswered.

The expression I started with was:

count(
aggr(
count
(DISTINCT
{$<TargetProductId={$(vTargets)},Segment={'1','2'},Product_Id={$(vBrand)},IsPersonAccount={'1'}>}
Account_Id)
, Account_Id)
)

The expression is correct for one month and is always correct in the total, however if I 2 or more months are added the months start cannibalizing off each other, but the total is still correct.

For the data in the example below the count per month should be: 2023-08: 4, 2023-09: 2, 2023-10: 2

Grateful if someone could push me in the right direction.👍

[theoat · Accepted solution]

I started from your formula by removing the set analysis and it seems correct to me.

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

[Stayhungry]

Hi Théo, you are right.

And oh no, its a bigger problem 🙂

I see that the issue is when I throw in the Product_Id for the brand discussion. I am adding the set analysis to ensure that in the activities with the accounts a specific product have been discussed (Product_Id) and that the Accounts are targeted on the TargetProduct_Id that is applicable to the Product_Id (Brand1 and TargetBrand1), and that the segment of the TargetProduct_Id is 1 and 2.

count(
aggr(
count
(DISTINCT
{$<TargetProduct_Id={$(vBrand1Target)},Segment={'1','2'},Product_Id={$(vBrand1)},IsPersonAccount={'1'}>}
Account_Id)
, Account_Id)
)

[Stayhungry]

Hi @theoat , thanks again for making me realize that the issue is in the set analysis. I have had a few more rounds of trying to understand what happens, not sure if this is enough information to provide further guidance, but here goes.

Set analysis evaluates over the selected months and provides a correct total but then in the months itself it does not distinguish if the targeted individual TargetProduct_Id={$(vBrand1Target)} has had an activity with Brand1 (Product_Id) or not in that specific month as long as a Brand1 activity existed within the time period (matching with any other Brand activity).

New Example: Account_Id 3, exist in Aug and Sep, the account is TargetProduct_Id={$(vBrand1Target)} and Segment={'1','2'}, the  Account has Brand2 activity in Aug and Brand1 activity in Sep. In the result I get the count in Aug instead of Sep. For the count I somehow need to ensure that the criteria in the set analysis is somehow fulfilled in the specific month. 

Edit1: Updated "New Example" text as I cant formulate a sentence...
Edit2: I will mark theoats answer a solution and open another question on the continuation