
1. Re: Getting a sum of weekly count distinct? Cant seem to do it
Łukasz Majewski Feb 26, 2016 12:08 PM (in response to Carlos Flores)pls share workbook as twbx

2. Re: Getting a sum of weekly count distinct? Cant seem to do it
Carlos Flores Feb 26, 2016 1:01 PM (in response to Łukasz Majewski)Here it is, i mocked it up to be a monthly view instead of weekly.
But the same answer would apply i believe

UniqueID Month.twbx 33.1 KB


3. Re: Getting a sum of weekly count distinct? Cant seem to do it
Łukasz Majewski Feb 26, 2016 1:12 PM (in response to Carlos Flores)
UniqueID Month.twbx 38.4 KB


4. Re: Getting a sum of weekly count distinct? Cant seem to do it
Peter Nettesheim Feb 26, 2016 1:46 PM (in response to Carlos Flores)1 of 1 people found this helpfulPerhaps 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..

5. Re: Getting a sum of weekly count distinct? Cant seem to do it
Carlos Flores Feb 26, 2016 2:00 PM (in response to Łukasz Majewski)1 of 1 people found this helpfulHello,
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

6. Re: Getting a sum of weekly count distinct? Cant seem to do it
Carlos Flores Feb 26, 2016 2:03 PM (in response to Peter Nettesheim)Your grand totals are not adding up though.
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
Peter Nettesheim Feb 26, 2016 2:04 PM (in response to Carlos Flores)1 of 1 people found this helpful 
8. Re: Getting a sum of weekly count distinct? Cant seem to do it
Carlos Flores Feb 26, 2016 2:08 PM (in response to Peter Nettesheim)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
Peter Nettesheim Feb 26, 2016 2:09 PM (in response to Carlos Flores)1 of 1 people found this helpfulThen you apply the suggestion by LUKASZ to your total if you want them to add up.

10. Re: Getting a sum of weekly count distinct? Cant seem to do it
Carlos Flores Feb 26, 2016 2:11 PM (in response to Peter Nettesheim)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
Eli Blankers Feb 26, 2016 2:25 PM (in response to Carlos Flores)1 of 1 people found this helpfulIn 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

13. Re: Getting a sum of weekly count distinct? Cant seem to do it
Eli Blankers Feb 26, 2016 2:45 PM (in response to Carlos Flores)2 of 2 people found this helpfulIF SIZE() = 1 THEN
COUNT(ID)/Unique_Weeks
ELSE
CNTD(Member Sk)
END
?

14. Re: Getting a sum of weekly count distinct? Cant seem to do it
Peter Nettesheim Feb 26, 2016 2:46 PM (in response to Carlos Flores)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.