Subscription-based Dashboard

[Werner95]

Hi Guys. We are a agri services company with various services that clients can choose from.

However. We have 1 service which is subscription-based.

Example:

Client has 1000 acres. He subscribes to this service of ours for 1 full year. Let's say he subscribed on the 2021/05 he then will be active until 2022/05.

A normal bar graph will show a big spike in Acres in May because that is when the Client signed-up for the service but this is not correct. I have to go split these Acres into weeks because our technicians service this 1000 Acres per week.

Outcome needed

Our Financial Year : Mar - Feb

I want to go look at all the Acres that are 'Active/Clients subscribed' with the dates I select. 

My Dates selector: sum({<dateEntry_det={">=$(vDateStart)<=$(vDateEnd)"},[serviceCode_det]={50}>} qtyAcres_det)

I hope this makes sense.

Thanks guys

[chris_djih]

So, if I understand you correctly you want to go from table1 to table 2:
table1:

Week	Client	Acre
1	ABC	1000
2	ABC	0
3	ABC	0
4	ABC	0

table2:

Week	Client	Acre
1	ABC	250
2	ABC	250
3	ABC	250
4	ABC	250

In this example i simplified that the subscription would only be 4 months, since i didn't want to wirte 12 rows.
So you want to distribute the initial value equally over the weeks.

[Werner95]

Correct.

I think to simplify for now. We can distribute into Months for a start.

Cheers

[chris_djih]

You only need the length of subscription in weeks. Either you already have this field, or you can calculate it with Enddate - Startdate.
then you make a straight table with Week and Customer as dimension and as measure:
=Sum(Acres) / SubscriptionWeekCount

[Werner95]

Hi

See here a table of the data after i've done what you said, I think:

HC: Client

Week: Week
Weeks: Week(Date Completed - Date Entry)

Total #ha: Total Acres for this Client

#ha / Week: Total Acres / Week(Date Completed - Date Entry)

Date Entry: Start Date

Date Completed: Date End

[Werner95]

@chris_djih is this what you meant?