topic Re: Automated count in QlikView

Automated count

dseelam — Fri, 26 Sep 2014 16:05:45 GMT

Hello Qlik Folks,

How can I auto count Id's for only last year, last quarter and last half year in straight or pivot tables using if condition or set analysis

Re: Automated count

Anonymous — Fri, 26 Sep 2014 18:36:22 GMT

Last Year: Count({<Year={$(=Max(Year))}>} ID)

Last Quarter: Count({<Quarter={$(=Max(Quarter))}>} ID)

Re: Automated count

anbu1984 — Sun, 28 Sep 2014 17:01:03 GMT

Last Year: Count({<Year={$(=Year(Today())-1)}>} Id)

Re: Automated count

MarcoWedel — Sun, 28 Sep 2014 17:12:28 GMT

please post sample data and expected result

thanks

regards

Marco

Re: Automated count

sathishkumar_go — Sun, 28 Sep 2014 22:19:56 GMT

Hi Dinesh.

Please post your application here.

-Sathish

Re: Automated count

dseelam — Mon, 29 Sep 2014 17:23:04 GMT

Hear is the sample QVW

you can see duplicate ID with different counts but what I want to see is single ID with sum of id life, 180 days and last trend, i don't want duplicate ID's

I am ready to remove SC Reading Date and Reading if they are the reason for duplicates but it collapse whole table

is there a possibility where I can remove them ?

Re: Automated count

dseelam — Mon, 29 Sep 2014 17:24:33 GMT

Can see the sample QVW

Re: Automated count

dseelam — Mon, 29 Sep 2014 17:24:42 GMT

Can see the sample QVW

Re: Re: Automated count

Anonymous — Mon, 29 Sep 2014 19:43:32 GMT

Hi,

I'm not sure what your expressions are about. To answer your last question I've attached an example how you can cut the dimensions to one ID Value. Maybe you can elaborate on the concept of your expressions and how this is reflected in your Data Model?

Best regards

Stefan

Re: Re: Automated count

MarcoWedel — Mon, 29 Sep 2014 21:56:06 GMT

Hi,

maybe the ID life column could be defined like (it seems to calculate an annual change rate of reading):

=(FirstSortedValue([Reading],-[SC Reading_DATE])-FirstSortedValue([Reading],[SC Reading_DATE]))/(Max([SC Reading_DATE])-Min([SC Reading_DATE]))*365

In your inital chart definition you have multiple occurences of ID because of multiple occurences of the SC Reading_Date dimension.

hope this helps

regards

Marco

Re: Automated count

dseelam — Tue, 30 Sep 2014 18:09:03 GMT

Hey Marco to some extent you are right but the id life numbers are wrong

I need sum of ID life

Re: Automated count

dseelam — Tue, 30 Sep 2014 18:09:41 GMT

Hello Stefan the expression is the main one all it matters

Thanks

Re: Re: Automated count

MarcoWedel — Tue, 30 Sep 2014 20:57:35 GMT

Hi,

maybe like this?:

hope this helps

regards

Marco

Re: Automated count

dseelam — Tue, 30 Sep 2014 21:34:17 GMT

Hello Macro the sum of ID Life (EXPRESSION)still does not match if you check ID 111111

APPRECIATE YOUR HELP