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

[ferrerosmoothie]

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.

[bharathadde]

Try this

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

The Only change I did is used = before $ sign

[ferrerosmoothie]

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

[Vegar]

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])

[ferrerosmoothie]

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

as expected. Unfortunately Maxstring doesn't work either.

[Vegar]

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'

[Vegar · Accepted solution]

You could try something like this:

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

[jonathandienst · Accepted solution]

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)

[ferrerosmoothie]

This works! Thanks a lot for the help.