topic Re: Discrepancies using filters - Set analysis formula. in Connectivity & Data Prep

Discrepancies using filters - Set analysis formula.

AN2024 — Sun, 02 Jun 2024 04:18:57 GMT

I have the follow formula to bring the new customer (clients who bought products in 2024 but didn´t in past years) without anyfilters it works correctly, however when I applied sales area filter and select for example xyz sales area it shows some customer as new one but it is wrong because some customer bought product in the sales area ZZZ in 2022 but in XYZ in 2024. In conclusion, I need to show the new customer based in the field "CUSTOMER" that´s mean if I select XYZ filter or whatever this kind of filter doesn´t need to affect the result.

Count({<CUSTOMER=e({<Year_Calday={">=$(=FirstSortedValue(Year_Calday, Year_Calday))<=$(=Year(Today())-1)"}>})>*<CUSTOMER={"=(Sum(Sales)>0)"}>} distinct CUSTOMER).

Thank you in advance

Re: Discrepancies using filters - Set analysis formula.

TauseefKhan — Sun, 02 Jun 2024 04:47:29 GMT

Hi @AN2024,
Check this:

Count(
{
<Year_Calday={"2024"},
CUSTOMER = e(
{<Year_Calday={">=$(=FirstSortedValue(Year_Calday, Year_Calday))<=$(=Year(Today())-1)">},
SALES_AREA=>}
),
SALES_AREA= >
}
DISTINCT CUSTOMER
)

******Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.********

Re: Discrepancies using filters - Set analysis formula.

AN2024 — Sun, 02 Jun 2024 15:46:18 GMT

@TauseefKhan Thank you for your help, below you can see the error showed in Qlik Sense.

Re: Discrepancies using filters - Set analysis formula.

TauseefKhan — Sun, 02 Jun 2024 17:05:11 GMT

Check this one :

Count(
{<
Year_Calday = {"2024"},
CUSTOMER = e(
{<
Year_Calday = {">=$(=FirstSortedValue(Year_Calday, Year_Calday))<=2023"},
SALES_AREA => // Ignore sales area selection for past years
>}
)
>} DISTINCT CUSTOMER
)

or

Count(
{<
Year_Calday = {"2024"},
CUSTOMER = e({<
Year_Calday = {">=$(=Max(Year_Calday) + 1)<2024"},
SALES_AREA = p() // Preserve current selection of SALES_AREA
>})
>} DISTINCT CUSTOMER
)

// Please provide sample data to validate the expression, or use the provided expression as a reference to resolve your issue.

*****Hope these steps will help you to resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.******

Re: Discrepancies using filters - Set analysis formula.

Vegar — Sun, 02 Jun 2024 20:15:50 GMT

@AN2024

It looks like a syntax error. Try to remove the marked '>' symbol in the screenshot.

Re: Discrepancies using filters - Set analysis formula.

AN2024 — Wed, 05 Jun 2024 18:25:43 GMT

@TauseefKhan This is a sample of the result, if I filter for X73 (Sales zone) it counts as new customer because there are not sales for the past year for this customer in the sales zone X73, nevertheless, that is wrong because I need to take in count a new customer only if they bought a product in the current year considering all sales zone and they didn´t buy a product in the past years, for example in this case the customer ID is not a new customer, because it bought a product in the sales zone X71 in 2022.

Sales zone	Date	Sales	CustomerID
X71	23/03/2022	3	13348
X73	29/05/2024	1	13348

Re: Discrepancies using filters - Set analysis formula.

AN2024 — Wed, 05 Jun 2024 18:27:45 GMT

Thank you, it brought a result removing the symbol ">" but unfortunately it is wrong as I explained to TauseefKhan:

"This is a sample of the result, if I filter for X73 (Sales zone) it counts as new customer because there are not sales for the past year for this customer in the sales zone X73, nevertheless, that is wrong because I need to take in count a new customer only if they bought a product in the current year considering all sales zone and they didn´t buy a product in the past years, for example in this case the customer ID is not a new customer, because it bought a product in the sales zone X71 in 2022."

Sales zone	Date	Sales	CustomerID
X71	23/03/2022	3	13348
X73	29/05/2024	1	13348

Re: Discrepancies using filters - Set analysis formula.

TauseefKhan — Thu, 06 Jun 2024 03:23:02 GMT

This formula will count distinct customers who made purchases in 2024 but did not make any purchases in previous years, regardless of the SALES_AREA selected.

Count(
{
<Year_Calday={"2024"},
CUSTOMER = e(
{<Year_Calday={"<=$(=Max(Year_Calday)-1)"},
SALES_AREA=>}
),
SALES_AREA= // This will ignore the SALES_AREA selection
}
DISTINCT CUSTOMER
)

Re: Discrepancies using filters - Set analysis formula.

AN2024 — Thu, 06 Jun 2024 14:11:26 GMT

Thank you for your help.