Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor II

## How to find/count number of days for selected months and year without AGGR

Hi,

I am using Sum(Aggr(Day(MonthEnd(Max(DateSet))), Month, Year)) to get total number of days for selected month and year. Can we use set analysis to achieve this?

Example : If I select Feb 2020, Mar 2020, Feb 2021, Mar 2021 , Result  - no of days = 119

If I select Jan 2020, May 2020. Result - no of days = 62.

Labels (2)

• ### Layout & Visualizations

6 Replies

perhaps like this: Max( DateSet) - Min(DateSet) + 1

talk is cheap, supply exceeds demand
Contributor II
Author

It's not working. Giving wrong answer. If i select Jan 2021 and Mar 2021 it's showing 90 days. But result should be 31(for Jan)+31(for Mar) = 62 days

Contributor II
Author

Can anyone give solution with if statement or set expression. Ques: Count number if days for selected month(multiple selection in list box)

EX: If Mar 2021 and 2020 are selected. Output should be 31+31 = 62 days

If Jan 2020, Feb 2020 and June 2020 are selected, then Output should be 31+29+30 = 90 days

I wouldn't try it in this way else using appropriate information within a master-calendar, like defining a 1 for each calendar/working-day and then just applying sum(DayValue) or you may also calculate the max/kum days there and using max/avg() to pick them.

- Marcus

Contributor II
Author

If i didn't have record on particular date then result is wrong. If May month has only records for 20 date. Then if I select May month it's show sum(dayvalue) is 20. But result I need to get is total days in month (i.e 31 days in May month)

MVP

HI @kiru_18

Hope you've Date field in your data model which is derived from Master Calendar.

In that case, You can use

count(Distinct Date)

Suppose, you don't have records in fact table & dimension table info, you can remove the those selections with help of set analysis.

Count({<YourDim1=, YourDim2=>}Distinct Date)

Else, you need to create dummy value for missed dates in your fact table to resolve this.

Thanks & Regards, Mayil Vahanan R