Count Distinct Date

[#1]

I am using the following formula to count distinct days in my data.

• Count(Distinct Date)

I want my count in the above formula to only change when I make a Year, Month, Quarter, Week etc. selection. Example:  Year: Count = 365, Month Jan: Count = 31, Week: Count = 7 and so on.

I do not these totals want this to change, if I make a selection in a field that is not a related to a date (Example: Customer).  What I want is the count to main the full count of days for date timeframe selected.  Example: With the above formula, if I select one Month; then select an attribute of another field within my data that only has one date associate with it, formula is only going to equal 1 when I want it to equal the total days in the Month of 31.

I have tried the following:

• Count({1}Distinct Date)  - This will only maintain the total distinct dates within the entire file.  It won’t change when I select Year, Quarter, Month, Week, etc.

Thanks in advance for your help.

Brian

[swuehl · Accepted solution]

Maybe like

COUNT({1<Year = $::Year, Quarter = $::Quarter, Month = $::Month, Week = $::Week>} DISTINCT Date)

[sunny_talwar]

Try this:

Count({1<Year = p(Year), Month = p(Month), Week = p(Week), Date = p(Date), MonthYear = p(MonthYear), Quarter = p(Quarter)>}Distinct Date)

Add all your Date related field like the above and it should work the way you want it to work.

HTH

Best,

Sunny

[#4]

This worked.  Thanks swuehi

[#5]

Hi swuehi,

The formula you provided works great in a text object but is not working in a Chart Object.  The Count is still reflected as the total number of dates associated with each field attribute.  Is there an alternative formula that can be used for a Chart Object?

Thanks

Brian

[swuehl]

You want it not to be split across dimensions?

Try the total qualifier:

COUNT({1<Year = $::Year, Quarter = $::Quarter, Month = $::Month, Week = $::Week>}  TOTAL DISTINCT Date)

[sunny_talwar]

In the chart you can use this:

COUNT(TOTAL {1<Year = $::Year, Quarter = $::Quarter, Month = $::Month, Week = $::Week>} DISTINCT Date)

[sasiparupudi1]

Hi

Are you looking something like below?

If Yes, you could use the functions

ex:

floor(YearEnd(SalesDate)-YearStart(SalesDate))

ceil(MonthEnd(SalesDate)-MonthStart(SalesDate))

ceil(WeekEnd(SalesDate)-WeekStart(SalesDate))

ceil(QuarterEnd(SalesDate)-QuarterStart(SalesDate))

where SalesDate is your Date Dimension

Hope this helps

[#9]

Thanks sunindia

That worked for the Chart

[sunny_talwar]

No Problem