topic Re: Set analysis for three condition matched in App Development

Set analysis for three condition matched

Cap — Tue, 21 Jun 2022 23:34:43 GMT

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 🙂

Re: Set analysis for three condition matched

sidhiq91 — Wed, 22 Jun 2022 04:04:32 GMT

Please try below and see if that helps.

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

Re: Set analysis for three condition matched

vinieme12 — Wed, 22 Jun 2022 05:45:59 GMT

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)

Re: Set analysis for three condition matched

Cap — Wed, 22 Jun 2022 10:34:05 GMT

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

Re: Set analysis for three condition matched

Cap — Wed, 22 Jun 2022 10:35:54 GMT

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 ! 🙂

Re: Set analysis for three condition matched

sidhiq91 — Thu, 23 Jun 2022 03:59:46 GMT

@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.

Re: Set analysis for three condition matched

vinieme12 — Thu, 23 Jun 2022 05:10:18 GMT

just replace the field names

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

Re: Set analysis for three condition matched

Cap — Thu, 23 Jun 2022 13:53:43 GMT

Working just fine 👍👍👍 thanks a lot

Re: Set analysis for three condition matched

Cap — Thu, 23 Jun 2022 13:55:14 GMT

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