sum of distinct counts

[#1]

Hi Steve,

Can someone  please guide me on the similar concept of how to aggregate the sum of distinct count of a column.

My Dates are between jan-2016 to jun-2017.  I need the sum (distinct count( write-offs) by dates selection. Below is the my count syntax. But I need sum( this counts syntax) it is throwing me an error saying we cannot nest aggregation.

= count(distinct{<NEW_MEND_DATE={">=$(vmin_mendate)<=$(vmax_mendate) )"}>} WRITE_OFF)

Thanks

Sowjanya

[krishnacbe]

Hi

Can you share the formula for  vmin_mendate & vmax_mendate?

[#3]

vmax_mendate_prev= ==Date(Max(NEW_MEND_DATE) - 1, 'MMM-YYYY')

vmax_mendate=MIN(Date([NEW_MEND_DATE], 'MMM-YYYY'))

= sum(Aggr(count(distinct(WRITE_OFF)), NEW_MEND_DATE={">=$(vmax_mendate)=$(vmax_mendate_PREV))"}>}))
HI, i'm getting the count , but somehow not showing the previous month count, when I select the maximum date.

example: sum(counts) for Junemonth = 14, then for my previous month it shoul do the sum(count) for may month =13. but is showing 14 only.

[krishnacbe]

Hi,

You need to exclude the Date selection. try below expression.

= sum(Aggr(count( {<NEW_MEND_DATE=, NEW_MEND_DATE={">=$(vmax_mendate)=$(vmax_mendate_PREV))"}>} distinct(WRITE_OFF))))
if it doesn't work, share some sample data and expected output to help you better.

[#5]

Hi Priya,

I need to calculate the sum of distinct counts of a variable( See the below sample data). this is a pivot data, but original data is based on the dates for more records based on a unique id.For the current month, I'm getting the sum based on the below syntax. When I again write the same syntax to get the previous month i.e. max of current month - 1 = Previous month, then also my sum is giving me the current month sum of distinct counts instead of last month counts.

SUM OF DISTINCT COUNTS FOR CURRENT MONTH:

NEW_MEND_DATE = Date(MonthStart([MEND_DT]), 'MMM-YYYY') as NEW_MEND_DATE

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

= sum(Aggr(count((WRITE_OFF)),NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate))"}))

SUM OF DISTINCT COUNTS FOR PREVIOUS MONTH:

NEW_MEND_DATE = Date(MonthStart([MEND_DT]), 'MMM-YYYY') as NEW_MEND_DATE

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

vmax_mendate_prev = =Date(Max([NEW_MEND_DATE]) - 1, 'MMM-YYYY')

= sum(Aggr(count((WRITE_OFF)),NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate_prev))"}))

DATE FORMAT OF NEW_MEND_DATE AND MY VARIABLE