dashboards based on country

[Harsha]

Hi Team,

We have requirement to create dashboards  based on country the client's IT professionals should be able to see the reports like Onboarded today, terminated today etc.Has anyone done this before. If yes please do share how did you filter it?

PS: Here I am not talking about manual filtering in analytics.

Thanks,

Harsha

[nimitdave]

You can create a user context analytics as below for the logged in users country.

select u1.USERKEY AS 'USERCONTEXT',u2.USERNAME,u2.COUNTRY,u2.FIRSTNAME,u2.LASTNAME
from users u1 inner join users u2 on u2.COUNTRY =u1.COUNTRY;

And then create a es-tile type dashboard and associate the above analytics with it.

[Harsha]

Hi Nimit,

Thanks for your quick reply if I put the same query it does give me users belong to that country. But if I apply it for dashboards like 1.Onboarded today 2. Onboarded in 30 days and so on it doesnt work.Could you please tell me how this can be solved?

Below is the query I tried:

select DISTINCT u1.USERKEY AS 'USERCONTEXT',u1.employeeid EmployeeID, u1.firstname FirstName,u1.lastname LastName,u2.country,u1.customproperty23 as 'Country Description', u1.createdate, CASE WHEN u1.statuskey IS NULL THEN 'Not Available' WHEN u1.statuskey = 0 THEN 'Inactive' WHEN u1.statuskey = 1 THEN 'Active' ELSE u1.statuskey END as userstatus, u1.startdate as StartDate, u1.employeeType as 'Employee Type' , u1.companyname 'CompanyName',u1.customproperty30 as 'UserSource' FROM USERS u1 inner join users u2 on u2.COUNTRY =u1.COUNTRY where u1.CREATEDATE like '2023-06-07%'

PS: I changed sav role from admin to end user to check if I can see that country users. Also dashboard was attached to that SAV role. 

Thanks,

Harsha

[DixshantValecha]

Please try the below query:-

SELECT DISTINCT u1.USERKEY AS 'USERCONTEXT',
u1.employeeid AS EmployeeID,
u1.firstname AS FirstName,
u1.lastname AS LastName,
u2.country,
u1.customproperty23 AS 'Country Description',
u1.createdate,
CASE
WHEN u1.statuskey IS NULL THEN 'Not Available'
WHEN u1.statuskey = 0 THEN 'Inactive'
WHEN u1.statuskey = 1 THEN 'Active'
ELSE u1.statuskey
END AS userstatus,
u1.startdate AS StartDate,
u1.employeeType AS 'Employee Type',
u1.companyname AS 'CompanyName',
u1.customproperty30 AS 'UserSource'
FROM USERS u1
INNER JOIN users u2 ON u2.COUNTRY = u1.COUNTRY
WHERE DATE(u1.CREATEDATE) = '2023-06-07';
 

In the modified query, I have used the DATE() function to extract the date portion from CREATEDATE, and then compared it with the desired date ('2023-06-07' in this case). You can replace the date value with your desired date range or parameterize it based on your requirements.

Make sure that the date format used in the query matches the format stored in the CREATEDATE column. If the column includes a timestamp, you may need to use additional functions like DATE_FORMAT() or adjust the comparison accordingly.