Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
AN2024
Contributor II
Contributor II

Discrepancies using filters - Set analysis formula.

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

Labels (1)
1 Solution

Accepted Solutions
TauseefKhan
Creator III
Creator III


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
)

View solution in original post

8 Replies
TauseefKhan
Creator III
Creator III

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

AN2024
Contributor II
Contributor II
Author

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

 

AN2024_0-1717341930226.png

 

TauseefKhan
Creator III
Creator III


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

Vegar
MVP
MVP

@AN2024 

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

Vegar_0-1717359334944.png

 

AN2024
Contributor II
Contributor II
Author

@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
AN2024
Contributor II
Contributor II
Author

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
TauseefKhan
Creator III
Creator III


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
)

AN2024
Contributor II
Contributor II
Author

Thank you for your help.