How to show active and inactive dealers based on last transaction

[Anonymous]

Hi,

Request your guidance to solve this issue

In my dashboard  I would like to show the dealers as "Active " and " Inactive" based on their last transaction date.  Say if any dealer  who has not done any transaction for last 30 days ( i.e. considering today as 20/09/2022, from 21/08/2022 till today ) then that dealer should be marked as "Inactive" in my "Table" visual and those who have transactions for the last 30 days then they should be displayed as "Active". 

Also, i need the count of "Active" and "Inactive" dealers  to be shown in Column chart visual

Kindly advise 

screenshot of my data

( sender = dealer) 

regards,

dsmitha

[PijushRoy · Accepted solution]

Hi @Anonymous 
Sorry my bad, I mistakely clicked on Solution button in place of reply button.

By the way, please try this

[Anonymous]

hi PijushRoy,

thanks a lot, it worked exactly as i wanted

double kudoz

regards,

dsmitha

[Anonymous]

hi PijushRoy,

no worries, in fact i was wondering did i click "accept a solution"  

i will check and get back to you soon 

thanks in advance

regards,

dsmitha

[PijushRoy]

Hi @Anonymous 

Please try to use this calculation, please change as per your column name

VAR today_ = TODAY()
RETURN
SWITCH(
TRUE(),
DATEDIFF(TransactionDate,today_,Day)>=30,"Inactive",
"Active")

Let me know, it works or not

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE 👍 this comment/Kudos 

[VahidDM]

Hi @Anonymous 

Can you post sample data (more data with 2 or 3 dealers) as text and expected output?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Relation between your tables

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Anonymous]

Hi VahidDM,

Furnished herewith details requested;

01) Sample Data

Created_Date	Time	sender
6/2/2022	12:09:19 AM	tigdealer_2004
6/2/2022	7:31:51 AM	tig-1070226
6/2/2022	8:15:29 AM	TIG_1044356
7/1/2022	12:04:17 AM	tig_khazimi
7/1/2022	12:47:19 AM	tigdealer_237
7/1/2022	7:31:31 AM	TIG_5123798
7/1/2022	8:12:08 AM	tig_1131687

02) Eg:   tig-1070226 was having sales in June 2022 but in July 2022 there was no sales,  for dealers who are not having sales for 30 days or more should be marked as "inactive" and for othes it should be marked as "active" dealers 

03) for this dashboard am using single table

regards,

dsmitha