
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
how to get count for the last week of each month
Hi,
Need some help to count of max available date of each month
In my Pivot table i have 2 dimensions as rows office and company and 1 dimension as column date and and 1 measure count(Office). and i created a new column month by using =date(monthstart(Date1,), 'MMM-YY') as i don't have month in my data (my source data is excel sheet). Now how can i calculate the max available date count of each month of each office.
my expression should pick max week(available date) of each month and show only that week count in the table.
with this i am getting the count for the 1st of each month but i need count for the last week of each month.
=Count({<"=WeekStart(TransDate)"={"=aggr(max(TransWeek), ""=MonthName(TransDate)"")"}>} Office)
Thanks
Rama


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share a sample app with expected output

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is my pivot table and i have Company ,Office as my rows and date and month as my columns (dimensions) and my measure is count(Office). i don't have any month filed in my data so i am getting the month field in set analysis with the expression:
=date(monthend(Mydatefield,), 'MMM-YY')
Here with this expression with date i am getting the last week of the each month :
aggr(max(WeekStart(Mydatefield)), "=MonthName(Mydatefield)")
and with this expression i am getting the count of first week of the month
=Count({<"=WeekStart(Mydatefield)"={"=aggr(max(Myweekfield), ""=MonthName(TransDate)"")"}>} Office)
My output should be the i need count for the last week of the month and not first week of the month.
Thanks in advance.
