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.
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
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])
(do whatever processing you need to do to report your starting averages for this accounting period)
(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.
sample data_temp A.twbx 50.1 KB
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
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.
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.
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 ?
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.
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
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?)
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
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.
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...