Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
ferrerosmoothie
New Contributor II

Getting the Daily Counts of IDs based on Max Date of Week

Hi, I have a data set of IDs with created and issued dates, and I want to get the work in progress (wip) counts for the last day of each week. I have daily counts as below. 

I can achieve this by hard coding the ending dates:

Count(
{<[DATETYPE]={'WIP'},
[FullDate]={'$(=timestamp('2/23/2019'))', '$(=timestamp('3/2/2019'))'}
>}
[ID]
)

But when I  use a concat function, it returns 0 counts. It only returns the ending date count if a single week in selected.

Count(
{<[DATETYPE]={'WIP'},
[FullDate]={'$(=concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), ',' ))'}
>}
[ID]
)

How can I achieve this using a concat function or other ways? Thank you.

Capturedata4.PNG

2 Solutions

Accepted Solutions
Partner
Partner

Re: Getting the Daily Counts of IDs based on Max Date of Week

You could try something like this:

$(=chr(39) & concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), chr(39) & ',' & chr(39) ) & chr(39))
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
MVP
MVP

Re: Getting the Daily Counts of IDs based on Max Date of Week

To keep the complexity under control, I would create a variable:

 

Set vDates = =chr(39) & concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), chr(39) & ',' & chr(39)) & chr(39);

 

Note that the above is a Set statement and the expression has a leading '=' sign. You can put this in a text box to confirm that the result is what you expect. Now the main expression becomes simply

 

Count({<DATETYPE = {'WIP'}, FullDate = {$(vDates)}>} ID)

 

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
8 Replies
bharathadde
Contributor

Re: Getting the Daily Counts of IDs based on Max Date of Week

Try this

Count({<[DATETYPE]={'WIP'},[FullDate]={'=$(=concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), ',' ))'}>}[ID])

The Only change I did is used = before $ sign

ferrerosmoothie
New Contributor II

Re: Getting the Daily Counts of IDs based on Max Date of Week

Thanks for the reply, but it doesn't work...still returning 0 values.

Partner
Partner

Re: Getting the Daily Counts of IDs based on Max Date of Week

It could be the fact that the modifier field is a dual value that is causing you trouble. When listing distinct values to compare you need to present the dual text value not the dual numeric value.

Check the output of your '$(=concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), ',' ))' .

You might be helped by changing the MAX to MAXSTRING like this.

Count({<
[DATETYPE]={'WIP'},
[FullDate]={'$(=concat(Aggr(MAXSTRING([FullDate]),[tMasterCalendar.Week #]), ',' ))'}
>}[ID])
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
ferrerosmoothie
New Contributor II

Re: Getting the Daily Counts of IDs based on Max Date of Week

The output of '$(=concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), ',' ))' is 

Capturedata5.PNG

as expected. Unfortunately Maxstring doesn't work either.

Partner
Partner

Re: Getting the Daily Counts of IDs based on Max Date of Week

Oh, thanks for the picture, I think I know why is not working. You need to figure a way to put apostrophes around each timestamp like this:

'2/23/2019 12:00:00 AM','3/2/2019 12:00:00 AM'

 

#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
Partner
Partner

Re: Getting the Daily Counts of IDs based on Max Date of Week

You could try something like this:

$(=chr(39) & concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), chr(39) & ',' & chr(39) ) & chr(39))
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
MVP
MVP

Re: Getting the Daily Counts of IDs based on Max Date of Week

To keep the complexity under control, I would create a variable:

 

Set vDates = =chr(39) & concat(Aggr(MAX([FullDate]),[tMasterCalendar.Week #]), chr(39) & ',' & chr(39)) & chr(39);

 

Note that the above is a Set statement and the expression has a leading '=' sign. You can put this in a text box to confirm that the result is what you expect. Now the main expression becomes simply

 

Count({<DATETYPE = {'WIP'}, FullDate = {$(vDates)}>} ID)

 

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ferrerosmoothie
New Contributor II

Re: Getting the Daily Counts of IDs based on Max Date of Week

This works! Thanks a lot for the help.