Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Patman
New Contributor

Count Column A if column B has 2019 in date

Please advise, I have dates like 12-Jul-2018, 04-Apr-2019....and I would like to count the number of 2019 items which have status approved.

 

5 Replies
trdandamudi
Honored Contributor II

Re: Count Column A if column B has 2019 in date

Please see the attached file and hope this helps...

 

 

Partner
Partner

Re: Count Column A if column B has 2019 in date

As @trdandamudi  put in his attached Qlikview file you could write something like this:  

 

=Count( {<Status={'Approved'},Year={2019}>} PR_1)

 

 

If you don't have an Year dimension then you could do like this:

 

Count( {<
  Status={'Approved'},
  Date ={">=$(=Date(YearStart(today()))) <=$(=Date(YearEnd(today())))"}
>}PR_1)

 

???
Celebrating my 300 accepted solution in the Qlik Community
???
Patman
New Contributor

Re: Count Column A if column B has 2019 in date

Cheers for that, Not working for me 😞 

What does the PR_1 do?

Partner
Partner

Re: Count Column A if column B has 2019 in date

It is the field in which used for calculation, you should adjust this to your own data model and field names.

The script generated by @trdandamudi in the qvw file looks like this:

 

SET DateFormat='DD-MMM-YYYY';

Data:
Load *,
Year(PR_1) as Year;
Load * Inline [
  Status,     PR_1
  Approved,   08-Aug-2019
  Denied,     04-Jul-2018,	
  Denied,     07-May-2019
  Approved,   06-Jan-2019
];

 

???
Celebrating my 300 accepted solution in the Qlik Community
???
Patman
New Contributor

Re: Count Column A if column B has 2019 in date

Cheers for the help, one of my fields had spaces so I was had to use [ 'XXX']

Many thanks :0