Set analysis for three condition matched

[Cap]

Hello,

This is my very first question on this forum and i might need some help with a set analysis syntax.

I try to filter two table (Invoice and Customer, linked with a CustomedID) and I want to know the sum of customer that have made an invoice during two specific year (for example 2019 AND 2020).

The Invoice table contains a Invoice_Year for that purpose.

For the information, I try to calculate a famous client KPI know as Client Retention

Thanks in advance 🙂

[sidhiq91]

Please try below and see if that helps.

Count({<Invoice_Year={'2019','2020'}>}distinct CustomerID)

[vinieme12]

as below

=Sum({<Customer_ID = p({<Invoice_Year={'2019'}>}Customer_ID)*p({<Invoice_Year={'2020'}>}Customer_ID)>} Sales)

Or if you have Customer_ID as a dimension in the chart

=Sum({<Invoice_Year={'2019'}>*<Invoice_Year={'2020'}>} Sales)

[Cap]

Thanks for replying but your syntax gave me "sum of customer that made an invoice in 2019 OR/AND 2020)


Here is a table i made, with a IF expression I can have the good result (Total = 574). I would like the same result with a set analysis syntax.

Thanks in advance

[Cap]

Not of them seem to work unfortunately (maybe the field didn't match up even if I try to adapt them).

I just posted a picture of a table with the result I try to obtain. Thanks a lot trying to help me ! 🙂

[sidhiq91 · Accepted solution]

@Cap  Please try the below Expression.

Count({<CompanyName=P({<Year={1997}>}CompanyName)>*<CompanyName=P({<Year={1998}>}CompanyName)>}distinct CompanyName)

But replace the dimensions as per your requirement. Let me know if it worked.

[vinieme12 · Accepted solution]

just replace the field names

=Count({<
Invoice = p({<Billing_Year={'2019'}>}Invoice)*p({<Billing_Year={'2020'}>}Invoice)
>} Invoice )

[Cap]

Working just fine 👍👍👍 thanks a lot

[Cap]

You're right, just had to use correct field and it gave me the good result. Thanks a lot 👍