, but I don’t have the field ‘ActiveMonth’(which is a field that counts the active months for each company_id), I only have the start date, and the end date of a plan, and using datediff I can’t get the result because the difference between start and end date, can be 1,3,6,12 months. Is there any way to get the monthly analysis without having that field ActiveMonth?
Hello @Juan, I think I understand what you are asking. You want it to recognize each month that the company_id was active and be able to count the number by the month, right? I’m not sure if this would work exactly with how your dataset is built, but you can try this: activeCompany = ifelse({dateField} >= {startDate} AND {dateField} <= {endDate}, 1, 0)
This works if you have a regular date field that you would use for your rows. You could even use the Month date type that you are showing above. Then you should be able to sum the number of companies that are active for each month in the pivot table. I will mark this as a solution for now, but if this doesn’t work as expected or isn’t the exact solution you were looking for, please let me know!
Hi Dylan, thanks for replying
My problem is that I only have the start date(MesRegistro) and the end date(next_billing_date), I really don’t know how to show this kind of analysis.
What I was doing before with dateDiff(MesRegistro,MesActivo, “MM”) I can’t do it because I don’t have the MonthActive field anymore, also I was using the company_id(count_distinct) field to ‘fill’ the fields.
Hello @Juan, I have a few questions to try and clarify. I think you will likely not be able to show your data how you want without the ActiveMonth field but I’ll see what we can figure out.
What exactly are you trying to display in your table? Is a user account connected to many company_ids? If that is the case, are you trying to show the number of months a company connected to a user account has been active? With some more details about what you are trying to accomplish we might be able come up with a plan, but without a concurrent date field it probably won’t work.
Hello @DylanM
what I want to show, is the amount of company_id(unique user key) of each cohort and its evolution in time(monthly).
maybe the table can not be done, but I would like to find a way to calculate this month by month, I already have the fields date month of registration, and next_billing_date, and I need to be able to calculate the activity of each company_id month by month, here an example to understand the problem
User 1 has 1 month plan
User 2 has 3 month plan
User 3 has 12 months plan
Hello
what I want to show, is the amount of company_id(unique user key) of each cohort and its evolution in time(monthly).
maybe the table can not be done, but I would like to find a way to calculate this month by month, I already have the fields date month of registration(MesRegistro), and next_billing_date, and I need to be able to calculate the activity of each company_id month by month, here an example to understand the problem
User 1 has 1 month plan
User 2 has 3 month plan
User 3 has 12 months plan
User account MesRegistro next_billing_date
User 1
2023-07-01 2023-08-01
User 2
2023-08-01 2023-10-01
User 3
2023-07-01 2024-07-01
now, my problem is that I cannot calculate user activity in the month to month table without a field that tells me that the user(company_id) is active between the start and end dates, so the 3 and 12 month users, although they are active users, I can’t find any way to consider them as such month to month.
Hello @Juan, the problem is, the way your data is currently set up is that if you use your Registered Date field as your date in your table, your company_id will only be compared to that exact date. You won’t know if it is active in Feb if they registered in Jan because it doesn’t exist on a row that includes February. You need to reformat your data. I am including a spreadsheet below to show you what you need. You will have to basically create a static month date that includes all company_id rows in each date. You can create this through unions in SQL. Since you lost your activeDate field, it will have to be done manually.
Then you can reference the static date month field in your calculated field like this: activeCompany = ifelse({staticDate} >= {MesRegistro} AND {staticDate} <= {next_billing_date}, 1, 0)
I know this isn’t the process you were hoping for, but this is the required solution if you want your pivot table to work the way you are expecting.
In this spreadsheet, startDate is MesRegistro and endDate is next_billing_date
Thanks for the reply @DylanM !
This wont work(because then i will have to deal with duplications, as the field MesActivo was a real problem when i have to show amounts or filter by some field) but i´ll manage to do it in a different way, thank you for the time!
