set analysis

yadav_anil782 — Tue, 03 Nov 2015 09:43:55 GMT

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

Re: set analysis

PrashantSangle — Tue, 03 Nov 2015 09:52:44 GMT

Hi,

try

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

Regards

Re: set analysis

mayankraoka — Tue, 03 Nov 2015 09:53:44 GMT

Hi Anil,

You can aggr by day :

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

Regards,

Mayank

Re: set analysis

Anonymous — Tue, 03 Nov 2015 10:04:43 GMT

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

Re: set analysis

Anonymous — Tue, 03 Nov 2015 10:21:32 GMT

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.

Re: set analysis

yadav_anil782 — Tue, 03 Nov 2015 11:19:56 GMT

Dear Balraj,

as per your logic result is in attached

Re: set analysis

Anonymous — Tue, 03 Nov 2015 11:29:46 GMT

Could you share sample excel for same?

topic Re: set analysis in QlikView

set analysis

Re: set analysis

Re: set analysis

Re: set analysis

Re: set analysis

Re: set analysis

Re: set analysis