set analysis

[yadav_anil782]

Dear Community,

      i have  user login details in one table and when i'm putting  "user_name" in dimention and  "=count(DISTINCT(LOGIN_TIME))" , its showing 53 count in chart which is correct as well because user logined 53 times in a day so its showing correct value . now i wants to short count(1) for  hole day .

example if user logined 53 times in hole day  then  result should be short day wise and in result will be only (1) count and if user logined 30  days no metter how many times but result should be 30 . plz help if anyone cane do this

year	month	day	Login Time	Date	USER_ID	USER_NAME
2011	May	11	16:23:43	5/11/2011	1453	B_SHARMA
2011	May	11	16:24:39	5/11/2011	1453	B_SHARMA
2011	May	11	17:20:12	5/11/2011	1453	B_SHARMA
2011	May	11	17:22:27	5/11/2011	1453	B_SHARMA
2011	May	11	17:23:41	5/11/2011	1453	B_SHARMA
2011	May	11	17:24:37	5/11/2011	1453	B_SHARMA
2011	May	11	17:25:10	5/11/2011	1453	B_SHARMA
2011	May	11	17:26:17	5/11/2011	1453	B_SHARMA
2011	May	11	17:27:28	5/11/2011	1453	B_SHARMA
2011	May	11	17:28:13	5/11/2011	1453	B_SHARMA
2011	May	11	17:29:01	5/11/2011	1453	B_SHARMA
2011	May	11	17:29:57	5/11/2011	1453	B_SHARMA
2011	May	11	17:30:36	5/11/2011	1453	B_SHARMA
2011	May	11	17:31:25	5/11/2011	1453	B_SHARMA
2011	May	11	17:32:09	5/11/2011	1453	B_SHARMA
2011	May	11	17:33:02	5/11/2011	1453	B_SHARMA
2011	May	11	17:33:39	5/11/2011	1453	B_SHARMA
2011	May	11	17:34:23	5/11/2011	1453	B_SHARMA
2011	May	11	17:35:09	5/11/2011	1453	B_SHARMA
2011	May	11	17:35:59	5/11/2011	1453	B_SHARMA
2011	May	11	17:36:45	5/11/2011	1453	B_SHARMA
2011	May	11	17:37:38	5/11/2011	1453	B_SHARMA
2011	May	11	17:38:18	5/11/2011	1453	B_SHARMA
2011	May	11	17:39:34	5/11/2011	1453	B_SHARMA
2011	May	11	17:40:11	5/11/2011	1453	B_SHARMA
2011	May	11	17:40:54	5/11/2011	1453	B_SHARMA
2011	May	11	17:41:33	5/11/2011	1453	B_SHARMA
2011	May	11	17:42:17	5/11/2011	1453	B_SHARMA
2011	May	11	17:42:55	5/11/2011	1453	B_SHARMA
2011	May	11	17:43:36	5/11/2011	1453	B_SHARMA
2011	May	11	17:44:24	5/11/2011	1453	B_SHARMA
2011	May	11	17:45:19	5/11/2011	1453	B_SHARMA
2011	May	11	17:46:16	5/11/2011	1453	B_SHARMA
2011	May	11	17:51:02	5/11/2011	1453	B_SHARMA
2011	May	11	17:52:07	5/11/2011	1453	B_SHARMA
2011	May	11	17:53:15	5/11/2011	1453	B_SHARMA
2011	May	11	17:54:27	5/11/2011	1453	B_SHARMA
2011	May	11	17:55:28	5/11/2011	1453	B_SHARMA
2011	May	11	17:56:44	5/11/2011	1453	B_SHARMA
2011	May	11	17:58:06	5/11/2011	1453	B_SHARMA
2011	May	11	17:59:13	5/11/2011	1453	B_SHARMA
2011	May	11	18:00:44	5/11/2011	1453	B_SHARMA
2011	May	11	18:02:13	5/11/2011	1453	B_SHARMA
2011	May	11	18:03:15	5/11/2011	1453	B_SHARMA
2011	May	11	18:04:18	5/11/2011	1453	B_SHARMA
2011	May	11	18:10:11	5/11/2011	1453	B_SHARMA
2011	May	11	18:11:22	5/11/2011	1453	B_SHARMA
2011	May	11	18:12:25	5/11/2011	1453	B_SHARMA
2011	May	11	18:14:24	5/11/2011	1453	B_SHARMA
2011	May	11	18:15:41	5/11/2011	1453	B_SHARMA
2011	May	11	18:17:31	5/11/2011	1453	B_SHARMA
2011	May	11	18:18:18	5/11/2011	1453	B_SHARMA
2011	May	11	18:21:24	5/11/2011	1453	B_SHARMA

[PrashantSangle]

Hi,

try

Sum(aggr(count(distinct User_Name),day))

Regards

[mayankraoka]

Hi Anil,

You can aggr by day :

Aggr(count(DISTINCT(LOGIN_TIME)),day)

Regards,

Mayank

[Anonymous]

Hi Anil,

Try this: count(aggr(count(distinct User_Name),Date))

The Date field can be converted to a date in the script as:

UserLogin:

LOAD year,

     month,

     day,

     [Login Time],

     date#(Date) as Date,

     USER_ID,

     USER_NAME

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

If I am not wrong, suggestion by Max and Mayank would give you again same result what you are getting.

I wud say take User ID as Dim and expression would be count(distinct(day)).

Try this.

[yadav_anil782]

Dear Balraj,

as per your logic result is  in attached

[Anonymous]

Could you share sample excel for same?