topic Re: how to take common customers between two years. in App Development

how to take common customers between two years.

Swathi — Fri, 15 Nov 2024 21:05:39 GMT

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)

Re: how to take common customers between two years.

Dataintellinalytics — Fri, 15 Mar 2024 05:48:05 GMT

Please share mocked data set.

Re: how to take common customers between two years.

LorantPataki — Fri, 15 Mar 2024 12:41:00 GMT

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)

Re: how to take common customers between two years.

Dataintellinalytics — Fri, 15 Mar 2024 12:51:53 GMT

Try using Set analysis intersection similar to below post.

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

Re: how to take common customers between two years.

Nagaraju_KCS — Fri, 15 Mar 2024 14:02:49 GMT

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))