13 Replies Latest reply: Sep 30, 2014 5:34 PM by Dinesh Reddy Seelam

# Automated count

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

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

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

• ###### Re: Automated count

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

• ###### Re: Automated count

please post sample data and expected result

thanks

regards

Marco

• ###### Re: Automated count

Hi Dinesh.

-Sathish

• ###### Re: Automated count

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: Re: Automated count

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: Automated count

Hello Stefan the expression is the main one all it matters

Thanks

• ###### Re: Re: Automated count

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

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

I need sum of ID life

• ###### Re: Re: Automated count

Hi,

maybe like this?:

hope this helps

regards

Marco

• ###### Re: Automated count

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