Here is the scenario:
- Make a transaction-based Google Sheet connected in Causal and set up this way:
- Then connect the sheet and map
Subscription to a Category.
- Then link the Google Sheet data to a variable called
ARR.
- Then create another variable with
ARR Last Data with formula last_data_timestep(ARR:all)
-
ARR Last Data will automatically be split up by Category Items Subscription because it uses ARR which is also having a Category.
- Now,
ARR Last Data will show the timestep equivalent of 4/10/23 date for ALL Category Items although for sub_1, the last transaction was 2/15/23 and for sub_2, the last transaction was 3/16/23.
This is a huge limitation for me right now and I think it is very misleading. I understand that changing this behavior may not be possible but if that is not possible, could we get a function last_transaction_timestep() or something else? Without knowing the actual date of the last transaction of each category item, I really have a hard time creating a model right now. If there is another way to get this timestep or to get the last transaction for each category item, please let me know.
Thanks in advance for your help.
Hey @Lukas_Gentele,
I understand that is frustrating and I have made a feature request to deal with the issue.
In the meantime, a workaround is that you could create a separate variable for each category item or Subscription. You can filter the data on each variable to only bring in the data for the specific item. Then you can perform the calculation you have above for each variable and the right answer for each will be given. Appreciate this could be tiresome if you have many category items but I think this is the best strategy for now 
Thanks. Yeah, it’s the only thing that is missing for us to pull the trigger on upgrading to Causal Business and really moving all forecasting and reporting over to Causal.
Unfortunately, creating separate formulas is not an option because there will be 50+ category items.
Thanks for creating the feature request. Any idea on the timeline?
Hey @Lukas_Gentele,
Had a further think on this and found a way we can calculate this right now!
If we can make the assumption that the last data timestep will have a positive value and all timesteps after that will be 0, with the ARR variable being connected to a data item like your example above,
we can write a formula like the below:
We can then create another variable which sums all these values to get to one value for each category item:
This will calculate the desired output.
So from this source data:
Causal uploads like this:
And we calculate this:
So with a little workaround I believe we can calculate what you need!
Please let me know if you have any questions and I have made the example model public so you can play with that here.
Thanks for the workaround. Unfortunately, that does not work because we have $0 a lot of entries (signaling that the customer churned).
If causal would let us find out if a value was 0 from the sheet or filled in as 0 because it wasn’t in the sheet at all, I could do a workaround similar to what you suggested but it’s all just 0 in causal unfortunately.
We can actually solve for that!
There’s a setting in Causal called Empty Value whereby you can ask Causal to treat Empty Values in data as a 0 or None. See docs. See snip below for how to access after right clicking on a variable:
So if we enable this setting on the ARR variable, empty values will come through as None and the 0 amounts will come through as 0.
We can then amend the calculated variable’s formula to this:
Which means that for a category like sub4:
The model correctly calculates the last data value:
Please check out the model and let me know if you have any questions!
Genius! That should do the trick! Thank you so much for diving into this!
Maybe you could help me with something else where I don’t quite know where to put it: I added a feature request which got shadow-banned (which I think you should disable in this community): https://community.causal.app/t/tabs-toggle-dropdown-for-chart-variants/457/1 (the post is still not visible and I have no idea why it got shadow banned in the first place)
Always glad to help! I hope you have the comfort you need to upgrade!
On your other post, sorry about that, not sure what happened. I’ve removed the spam tag and will reply in thread.
