-
1. Re: Retrieval of Max Record then aggregating by this value!
pooja.gandhi Feb 10, 2016 7:42 AM (in response to Karan Parekh)1 of 1 people found this helpfulHey Karan!
You can create a calculated field like this:
{fixed [Company ID], [Current Team], [Date (Month / Year)] : sum( IF [Date] = { fixed [Company ID],[Current Team], [Date (Month / Year)] : max([Date]) } then [Salesrep ID] end ) }
It it difficult to explain each bit of the calculation, so I suggest dragging all the pills of interest and taking bits and pieces of this nested calc and see how it works, additionally I did drag your date dimension in filter and only selected march and april. I kept rest of your calcs the same and I am assuming a label of 'Loss' in your snapshot above for sales rep 1312 was a typo, because the variance is positive?
If you don't understand something, let me know.
Hope this helps!
Pooja.
-
2. Re: Retrieval of Max Record then aggregating by this value!
Karan Parekh Feb 10, 2016 8:17 AM (in response to pooja.gandhi)Hi Pooja,
The Loss above in the Excel snapshot was 100% a typo lol. Thank you very much for pointing this out!!
Thank you so much for taking the time out to view this!! I have a couple of questions/comments:
- I don't think I can use a date field in my filter - unfortunately that is why I set up the calculated fields of April $$ and March $$. Reason being - I'm doing this analysis for the past 15 months.
Would foregoing the date filter affect the results here? What if I tried to bake that logic into the LOD formula to take into fact that we max the date CASE WHEN date between 03-01 - 03-31?
-Everything you did here makes 100% sense - but why is the grand total showing 0% churn? Ideally, it should be able to sum the LOSS / March Revenue = 11% Churn Rate. Should we be adding something to L.O.D.? Or is my IF statement for labeling messing this up?
Let me know your thoughts Pooja. You have no idea how helpful this is.
Best,
Karan -
3. Re: Retrieval of Max Record then aggregating by this value!
Karan Parekh Feb 10, 2016 10:17 AM (in response to pooja.gandhi)Hey Pooja Gandhi,
Just an additional follow up question - did I miss something here? I have attached my workbook as well. I tried to mess around with the loss formula to readjust the total LOSS (since it should not be this number) but to no avail. let me know if you could work some magic!
Best,
Karan
-
4. Re: Retrieval of Max Record then aggregating by this value!
pooja.gandhi Feb 10, 2016 10:24 AM (in response to Karan Parekh)1 of 1 people found this helpfulHey Karan!
I still have to look at the churn rate question. The formula for Loss revenue would be:
IF [Variance] < 0 then [Variance] elseif [Variance] > 0 then 0 end
No LOD required for that one. Also if you do not want to place your date in the filter shelf, you can actually drag the label in filter and exclude 'stagnante' that serves the same purpose. The thing I do not understand is, if you are looking at past 15 months data, are you going to create 15 calculations for each month? That seems to be an overkill, but I do not know the full story so you may be right in what you need to do.
Hope this helps!
Pooja.
-
5. Re: Retrieval of Max Record then aggregating by this value!
Karan Parekh Feb 10, 2016 10:49 AM (in response to pooja.gandhi)Pooja Gandhi Ha! Unfortunately yes, the set of calculations for each month. The goal is to see a rolling 15 month rate of revenue loss. We used to use spreadsheets (where literally anything can be done) but now trying to incorporate logic into Tableau.
It is arduous, but rewarding at the same time. You see those big spikes in the beginning of the quarter? That is where the companies are shuffled to different owners...hence the reason I was trying to max the date for the right sales rep to inherit the loss.
I have one more question regarding LOD - if I remove the advertiser or what not - I see that [Current Team] is fixed in the Calculation1. I am trying to work it out on my end so that the -335 is included in the grand total instead of the variance, but I think this is related to the bigger problem of the grand total not adding up.
Let me know your thoughts? I attached my workbook once again.
Best,
Karan -
6. Re: Retrieval of Max Record then aggregating by this value!
Karan Parekh Feb 15, 2016 3:35 PM (in response to Karan Parekh)I appreciate your help Pooja Gandhi - I tried to reconcile the grand totals to reflect the true churn % but couldn't find a way. Did you find any success in doing so?
Has anyone a solution that can supplement / enhance the ideas laid out here?
Best!
Karan