Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Swathi
Creator
Creator

how to take common customers between two years.

i have tktdate, soldtodesc, delvqty field's, i need to calculate common customers falling in current ytd(01-01-2024 to 29-01-2024) and previous ytd (01-01-2023 to 29-01-2023) and volume>0.

How to write expression for these. i tried below but getting error

Count({<Period={'Month-Year'}, tkt_date={"$(PYTD)"},tkt_date={"$(PYTD)"},delv_qty={">0"}>} DISTINCT sold_to_desc)

Swathi_0-1706614935864.png

 

 

 

Labels (2)
4 Replies
Dataintellinalytics

Please share mocked data set.

LorantPataki
Contributor III
Contributor III

Hi @Swathi ,

this is my take on such a scenario (please replace with your field, variable and preferred Measure names):

Count(distinct {<[Customer] = {"=SalesPrior <> 0 and SalesActual <> 0"}>} [Customer])

where I've created SalesPrior and SalesActual as master measures, to keep things cleaner.

SalesActual is Sum({<[DateField] = {"$(CurrentYTD)"}>} SalesField)

SalesPrior is Sum({<[DateField] = {"$(PriorYTD)"}>} SalesField)

Dataintellinalytics

Try using Set analysis intersection similar to below post.

https://community.qlik.com/t5/QlikView-App-Dev/Set-analysis-intersection/td-p/391681

 

qv_testing
Specialist II
Specialist II

may be this

count (distinct {< sold_to_desc= P ({<tkt_date={"$(YTD)"},delv_qty={">0"}>} sold_to_desc) >*< sold_to_desc= P ({<tkt_date={"$(PYTD)"},delv_qty={">0"}>} sold_to_desc) > } sold_to_desc))