topic Re: Set Analysis - Count the Active Dealers in App Development

Set Analysis - Count the Active Dealers

AjjuSid — Mon, 29 May 2023 05:09:45 GMT

Hi All,

I am trying to get the count of active dealers in my report and need help with the Set Analysis expression. Need to get the Count of Dealers having at least 1 contract produced since 2022.

This way I can eliminate the Inactive dealers who haven't produced a contract since last year.

Fields = Contract ID (Fact Table), Dealer ID (Dim Table, associated with fact based on DEALER_ID), and Date(Fact Table).

I tried the below expression, but it didn't help me.

count({<[Dealer ID] = {"=sum({<[ Date] = {'>=01/01/2022'}>}[Contract ID]) >0"} >} distinct [Dealer ID] )

Please let me know if you need further information.

can the experts please help with the question. Thanks
@sunny_talwar @swuehl @tresB

Re: Set Analysis - Count the Active Dealers

tresB — Mon, 29 May 2023 08:10:36 GMT

A quick try; for search string try a valid quote other than single quote. Also sometimes date format could lead to the issue. Try like below and see if helps, if not we have to dive deep.

count({<[Dealer ID] = {"=sum({<[ Date] = {`>=MakeDate(2022)`}>}[Contract ID]) >0"} >} distinct [Dealer ID] )

Note : in red I am using backquote

Re: Set Analysis - Count the Active Dealers

BrunPierre — Mon, 29 May 2023 08:03:09 GMT

By using double quotes, it will be interpreted as a search value.

count({<[Dealer ID] = {"=sum({<[ Date] = {">=01/01/2022"}>}[Contract ID]) >0"} >} distinct [Dealer ID] )

Re: Set Analysis - Count the Active Dealers

AjjuSid — Mon, 29 May 2023 09:47:22 GMT

Backquote did really help, but the issue was mainly because of the date format- I had to use 01/01/2022 instead of Makedate(2022).

Thanks for your prompt reply, this really helped.