1 2 Previous Next 16 Replies Latest reply on Feb 26, 2016 3:05 PM by Carlos Flores

# Getting a sum of weekly count distinct? Cant seem to do it

I want to get a total count of Count Distinct based on a weekly pull.

If i do a basic count distinct, ill get only the distinct values for the whole time period. I want to break down the value by week then add it, can anyone help me out?

Ideally i want to get that 3,353 value and do some calculations to it, but the only way to get it is to break down the count by week and add a grand total.

Any ideas?!

• ###### 1. Re: Getting a sum of weekly count distinct? Cant seem to do it

pls share workbook as twbx

• ###### 2. Re: Getting a sum of weekly count distinct? Cant seem to do it

Here it is, i mocked it up to be a monthly view instead of weekly.

But the same answer would apply i believe

• ###### 3. Re: Getting a sum of weekly count distinct? Cant seem to do it

If you want to sum up weekly distinct counts then this should do:

I saved your workbook in v9.2

1 of 1 people found this helpful
• ###### 4. Re: Getting a sum of weekly count distinct? Cant seem to do it

Perhaps you want to use a Level of Detail calculation if you are looking for a total to use in calculations without having to get at it with the Grand Total. Just consider adding filters to context where appropriate so the filters are applied first if that is your intention. Here are a few quick screenshot that may work - just showing that the filters apply first to the LOD calculation if you have added the filter to context..

1 of 1 people found this helpful
• ###### 5. Re: Getting a sum of weekly count distinct? Cant seem to do it

Hello,

I can already do this, but i need to have the Grand Total (6) as a calcualted field.

Id like to have for example, the average count distinct by month.

Id need the (Grand Total)/Weeks

This is what im having trouble with.

Is there a way to create a calculated field that would output the 6?

SUM(COUNTD([Value]))/Weeks is what i want, but i want the countD to add each week

1 of 1 people found this helpful
• ###### 6. Re: Getting a sum of weekly count distinct? Cant seem to do it

If you have 15 distinct for each week, id need the total to be 45

• ###### 7. Re: Getting a sum of weekly count distinct? Cant seem to do it

It seems like a Window function might do the trick.

1 of 1 people found this helpful
• ###### 8. Re: Getting a sum of weekly count distinct? Cant seem to do it

Hmm if you have a count of 15,13,12,19 the total would have to be 59.

Also how would u set the window_avg to look at the countd weekly?

• ###### 9. Re: Getting a sum of weekly count distinct? Cant seem to do it

Then you apply the suggestion by LUKASZ to your total if you want them to add up.

1 of 1 people found this helpful
• ###### 10. Re: Getting a sum of weekly count distinct? Cant seem to do it

okay here is what i need, i need that total (6) and divide it by months.

How do you take a grand total and do calculations to it?

• ###### 11. Re: Getting a sum of weekly count distinct? Cant seem to do it

In order to override the grand total calculation, you have to specify the grand total as something different than the rest of the column.  Use the table calculation, Size().

IF SIZE() = 1 THEN

*Whatever you want the grand total to calculate*

ELSE

*Whatever the normal calculation is*

END

Let me know if that helps.

Cheers,

Eli

1 of 1 people found this helpful
• ###### 12. Re: Getting a sum of weekly count distinct? Cant seem to do it

IF SIZE() = 1 THEN

COUNTD(id)/Unique_Weeks

ELSE

Blank?

END

Given i have the view above created, but i need to divide that 3,353 by the # of weeks, how would i use the Size?

where does it get added into

• ###### 13. Re: Getting a sum of weekly count distinct? Cant seem to do it

IF SIZE() = 1 THEN

COUNT(ID)/Unique_Weeks

ELSE

CNTD(Member Sk)

END

?

2 of 2 people found this helpful
• ###### 14. Re: Getting a sum of weekly count distinct? Cant seem to do it

Because you are trying to do an aggregate on an aggregate, you may need to perform the analysis on the whole window, but only show a single value. Not sure if this will get you where you need to go, but perhaps it is a jumping off point or will spark an idea that allows you to solve your specific issue.

1 2 Previous Next