1 2 Previous Next 21 Replies Latest reply on Sep 1, 2015 5:00 PM by Madhu Pimple

AVG should consider the total users it considers only the filtered ones

Basically I am trying to find the average.... based on the u id...month of start date and month of completed at...  If they dont have any completed assessmnt they still should be considered and the count should be 0...

Please check the sheets numerator and denominator.....ex. one user - user id= 10... he started in june....

but he completed assessment in august...but he should be counted for june, july also....

so now for this my output should be

I not sure if I am conveying to you correctly but basically I am trying to achieve the average...based on the total users and completed assessment...if the users have not completed any of that month then count them but their assessment should be 0

Looking forward for help

• 1. Re: AVG should consider the total users it considers only the filtered ones

You are right. If you filter rows out with a quick filter, then AVG will only compute for the rows that get through the filter.

If you want to include all rows in a table calculation of some sort, but only display a subset of those rows, then you can exclude rows for DISPLAY (but not exclude for calculations) by filtering with a table calc.

Usually a table calc with LOOKUP works for pulling which rows to display.  And you can embed "IF" logic in a lookup to grab the rows you actually want to display.

• 2. Re: AVG should consider the total users it considers only the filtered ones

Hey Joe Oppelt

Thanks for the response

Can you tell me if it gets included in calculating average... ?

if the user has completed assessment or its incomplete or there is no record....

You can check my sample worksheet in the main thread

Thanks in advance

• 3. Re: AVG should consider the total users it considers only the filtered ones

Madhu -- I moved this forward a bit.

You can't divide the numbers from one sheet by the numbers in another sheet.  You need to generate those numbers all on the sme sheet.

I created a calc field that does the same thing as te quock table calc on the Demoninator sheet.  I added it to the denominator sheet to show that it does the same thing.

I was then going to add it to the numerator sheet, but I noticed that you are generating the numerator values along different dates than the numerator values.  So in effect you are comparing apples and oranges.

If I were working on that project, I would want a third date field.  Something like an accounting date.  Then I would run my screens along that, and have calcs that would look like this:

Start Date Calc:

If Month([Start Date]) = MONTH([Accounting Date]) and YEAR([Start Date]) = YEAR([Accounting Date])

then

(do whatever processing you need to do to report your starting averages for this accounting period)

END

(And you would do the same for [Completed Date].)

Then, for a given accounting month, you would have your start activity and end activity, and you could divide your numerator by denominator with valid meaning.

1 of 1 people found this helpful
• 4. Re: AVG should consider the total users it considers only the filtered ones

Hey Joe Oppelt

Actually the start date is kind of start date in the course(program).. it is like users have started that course....

and has completed various assessment in months or may be not completed...

so my problem is I want to do  an AVG who completed the assessments divide by total number of users.

for eg. user 10- he has started in course in june but he completed assessment in august...

so wen we do the average in june and july he should be counted but his assessment completed will be 0 for those months and for august he completed 1 assessment...

I hope you have understood my problem...

Looking forward for your reply

• 5. Re: AVG should consider the total users it considers only the filtered ones

Do you have any thoughts on this ?

Please help..

• 6. Re: AVG should consider the total users it considers only the filtered ones

I don't know where to go with this.  The way your data is set up makes it impossible to do what you want to do.

Look at User10.  Start date is in June.  End date is in August.  There is no month marker for July, but you want to display July data.

Tableau won't make rows for you.

But if you had a reporting-month dimension to report against (perhaps in another table) you could blend your current data and report against it.  That would at least let you force a July mark on the viz for that user.  You would also have to create some kind of calc that tells Tableau to include that user in July counts.

Also, I see you have users doing multiple assessments.  Can you also have multiple users going a given assessment?

As far as I can see, there are too many unanswered questions to figure out how you shouild change the way your data is shaped.  As it stands, you have too many moving parts to use it the way it is shaped now.

• 7. Re: AVG should consider the total users it considers only the filtered ones

But to answer your initial question, to divide numbers from one sheet by numbers from another, you need to generate both sets in one sheet.  Then you can divide them.

Until you are running along a common date dimension, though, you can't generate both on one sheet.

• 8. Re: AVG should consider the total users it considers only the filtered ones

Joe Oppelt yes I got it what you want to say...and yes I can have multiple users going in given assessment...

So to consider that user should be included in the count of july and june... can we use running count ? based on fixed dimension of user and start date ?

• 9. Re: AVG should consider the total users it considers only the filtered ones

You have to have rows for each month, for each assessmsnt, for each user.  That's what's missing in the way your data is set up.

You can't do a running count if you don't have data (even a row with a null value) in a given month.

• 10. Re: AVG should consider the total users it considers only the filtered ones

Ohkay I thought I can do that running count like I did it in the denominator sheet.... so I got values 1,3 and 5....

I am wondering how I should connect both the dates together....course start date and completed at

• 11. Re: AVG should consider the total users it considers only the filtered ones

Yes, you can do a running count for START.

AND you can do a running count for END.  You get June-Aug for START becaue you have data for Jun-Aug.  But you only get Jul-Aug on the other sheet because you only have data in the end date for Jul-Aug.  And when September comes around, and if someone finishes something on Sept 2, but no new assessments start until Sept 15, you won't have Sept in the Start dates...  But you want to jave June through Sept on your chart...

That's why you need one common date to run these along -- a date range that include all possible START and END dates.    That's what I've been saying.  Optimally, you should have a row for each user for each of the intervening months, such as we see with User10.  (Theoretically, what if he took 10 months to complete the assessment?)

• 12. Re: AVG should consider the total users it considers only the filtered ones

hmm....But in my case duration doesnt matter...what matters is the completion date or u can say the month when he completed the assessment.... start date is something like... he has enrolled in the program...like in school program....

and from now he is taking the assessment so I wanted the "averg of assessment completed per user per month"...

I am going nuts with this

• 13. Re: AVG should consider the total users it considers only the filtered ones

I thought you wanted User10 to be counter as sero completed in July.  He started in June.  Zero complete in June.  He didn't finish until August.  Zero completed in July.  You have to count him for July to get a "per user" total, but what do you have to count?  There is no record there to show that he is still open in July.

• 14. Re: AVG should consider the total users it considers only the filtered ones

thats correct... So if there is no record it says that he didn't complete any assessment in that month... but when we take the total user count he should be considered.. as we are doing assessment completed per user per month...

1 2 Previous Next