topic Re: set expression in QlikView

set expression

yadav_anil782 — Mon, 02 Nov 2015 10:08:16 GMT

Dear Community,

i'm using this expression for count the user login time on time dimension =count([Login Time]) and result is showing with hole day user login .

i need if user login 10 times in a day then count should be not reflect 10 it should be reflect "1" for hole day. and i wants to show this user login count on time dimension (year,month,day)

i attached my excel sheet for data reference , plz help if anybody can help in this .

Re: set expression

mayankraoka — Mon, 02 Nov 2015 10:36:06 GMT

Hi Anil,

Please try this:

aggr(count(distinct([Login Time])),USER_ID,LOGIN_TIME)

Regards,

Mayank

Re: set expression

rubenmarin — Mon, 02 Nov 2015 10:36:12 GMT

Hi Anil, I don't kwon how you want to show the data, please check attachment. To count differente dates you can make a count(DISTINCT ...)

Re: set expression

mayankraoka — Mon, 02 Nov 2015 10:44:00 GMT

Hi Anil.

pfa.

Regards,

Mayank !

Re: set expression

yadav_anil782 — Mon, 02 Nov 2015 11:01:03 GMT

Dear Ruben,

i want short count on day basis only , if i login 10 times in particulate day then it should be consider only "1"

Re: set expression

Anonymous — Mon, 02 Nov 2015 11:04:22 GMT

In that case you need to change your dimension from Time to UserID/Name etc.

If you are taking Time Dimension, if this time match to your expression value it would return 1.

So use User Dim.

Re: set expression

rubenmarin — Mon, 02 Nov 2015 11:08:17 GMT

Hi Anil, have you checked the attachment? It only counts 1 by each user/day, If you want to discard the user you only need to remove the user dimension in the attachment.

Re: set expression

ankitaag — Mon, 02 Nov 2015 11:26:55 GMT

Hi Anil,

In your excel you have 2 fields Login time and LOGIN_TIME. Can't you take LOGIN_TIME in count as count(distinct LOGIN_TIME)?

Thanks and Regards,

Ankita

Re: set expression

Anonymous — Mon, 02 Nov 2015 13:18:05 GMT

Anil,

Use scatter chart with two dimension.

One dimesion wud be: USerID

2nd wud be: Time Dim

Expression wud be: count(distinct(LOGIN_TIME))

Re: set expression

jafari_ervin — Mon, 02 Nov 2015 13:53:52 GMT

you can use this expression count( distinct date([Login Time]))

Re: set expression

yadav_anil782 — Tue, 03 Nov 2015 05:05:22 GMT

Dear All,

i applyed the all suggestions but this issue still not resolved .

now plz suggest to wright group by script , may be it can work-

xx_login_rev_view:

LOAD USER_NAME,

USER_ID,

date( LOGIN_TIME) as LOGIN_TIME,

LOGIN_COUNT

FROM

(qvd);

now i want group by -"USER_ID" for " Login_TIME "

so date_time can be group day wise