Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor

## How to correctly use Mid function in set expression

Hi All,

I have a requirement where i need to extract the month/period part from a key containing year-month-week.

This needs to be done in a chart which has to show all records irrespective of any period/week selection.

In the attached file, i am using the following expression to count the number of weeks in the corresponding period in chart:

count({<Period={"=mid(PeriodWeek,6,3)"}>}distinct Week), however it is always returning 0.

Thanks,

Reema

3 Replies
MVP

Since you are looking to evaluate the condition on row by row basis, set analysis in not going to work for you.... You will either need to create a flag in the script or use an if statement to do this

Count(DISTINCT If(Period = Mid(PeriodWeek,6,3), Week))

Contributor
Author

Thanks, however I need to display count of weeks as total number of weeks in that period, for eg. all rows with  P01 should display 4 and P03 should display 5.

I tried disregarding the dimension with the following, it is not working:

Count({<Week=,Period=,PeriodWeek=>}DISTINCT If(Period = Mid(PeriodWeek,6,3), Week))

MVP

This?

Aggr(Count(TOTAL <Period>DISTINCT If(Period = Mid(PeriodWeek,6,3), Week)), PeriodWeek, Period)

Community Browser