Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
Can you share the formula for vmin_mendate & vmax_mendate?
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.
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.
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