topic Re: Set Analysis User Name by Selected Year in QlikView

Set Analysis User Name by Selected Year

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to count by selected year where the "Created" year equal to YEAR.

Currently using the calculated dimension as below:

=Aggr(Only({$<Year={"$(=Date(Created,'YYYY')"}>} [User Name]), [User Name])

Expression:

Count([AR ID])

But its returning me null.

Can you please advise where I am having the issue.

Please see the attached sample qvw file.

Thanks & Regards,

Farrukh

Re: Set Analysis User Name by Selected Year

YoussefBelloum — Fri, 23 Mar 2018 16:33:11 GMT

Hi,

it will not work because your User name and AR_ID are not linked..

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 16:37:00 GMT

Hi Youssef,

AR_ID is not a key value, the key Value is "CREATED" field. which is linked to master calendar.

Farrukh

Re: Set Analysis User Name by Selected Year

sunny_talwar — Fri, 23 Mar 2018 16:38:57 GMT

May be you need Lower function in your tblInfoPathUsers table also

Re: Set Analysis User Name by Selected Year

YoussefBelloum — Fri, 23 Mar 2018 16:42:09 GMT

When I say Linked I'm not talking about joining keys..

on your data, every User name don't have any AR_ID.

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 16:46:10 GMT

Hi Sunny,

I have put the lower function but still no luck.

The problem I am having with Year field. When I select Year then in a char its calculate 0. When I remove year selection then its show "//Error in calculated dimension".

Can you please see the calculated dimension? I do not know where I am having the issue in syntax.

Re: Set Analysis User Name by Selected Year

sunny_talwar — Fri, 23 Mar 2018 16:47:38 GMT

Can you attach the new app where you have fixed the issue related to lower?

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 16:50:15 GMT

Hi Youssef,

There are only 3 ARID where the user is not assigned. every single record has the AR ID

Farrukh

Re: Set Analysis User Name by Selected Year

YoussefBelloum — Fri, 23 Mar 2018 16:51:44 GMT

for me the problem is here:

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 16:53:26 GMT

Hi Sunny,

Please see the attached sample file.

Farrukh

Re: Set Analysis User Name by Selected Year

sunny_talwar — Fri, 23 Mar 2018 16:59:33 GMT

Created is a timestamp field... convert this into just date before you create your calendar and also create this into date in your tblInfoPath table

Date(Floor(Created)) as Created in both places is needed

Re: Set Analysis User Name by Selected Year

sunny_talwar — Fri, 23 Mar 2018 17:00:43 GMT

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 17:01:01 GMT

Hi Youssef,

Please see the below snapshot:

User_1 has many ARIDs. and AssignTO is a key value which is link to Info table.

Farrukh

Re: Set Analysis User Name by Selected Year

YoussefBelloum — Fri, 23 Mar 2018 17:10:47 GMT

here it is ok.. but on your table you have:

dimension = user_name

measure: AR_ID

the green data are ok

the red data cannot work

Maybe I'm missing something..

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 17:11:57 GMT

Thanks Sunny,

Now its giving me the correct counts. but its still not showing the dimension:

=Aggr(Only({$<Year={"$(=Year(Created)"}>} [User Name]), [User Name])

And giving me //Error in caluculated dimension.

Farrukh

Re: Set Analysis User Name by Selected Year

sunny_talwar — Fri, 23 Mar 2018 17:12:28 GMT

Can you re-attach please

Re: Set Analysis User Name by Selected Year

Anonymous — Fri, 23 Mar 2018 17:38:06 GMT

Thanks Sunny, This has been sorted, instead of calculated dimension I use just a normal field as a dimension.

Thanks for all you great help.

Kind regards,

Farrukh