Monthly revenue forecast from monthly fee

[gcorazza]

Hi,

So I have a dataset in which I have the following essential information:

[Customer ID], [Contract number], [Contract begin date], [Contract end date], [Rate description], [Rate price]

[Rate price] is the monthly fee amount that customer pays to receive a service, and this price is valid between [Contract begin date] and [Contract end date]. [Rate description] is to describe if the [Rate price] is referring to a fixed monthly fee or to a variable monthly fee, which depends on customer [Avg usage] of the service (this info comes from another table, based on [Customer ID]).

[Customer ID] is unique per customer, but it may have many instances in this dataset because they may have old contracts that are already expired. [Contract number] is also unique, but it may have more instances as well due to different [Rate description]. EVERY customer has both [Rate description] types: fixed and variable.

So my dataset does not have the monthly results, what it has is the monthly fee and the period in which this fee is valid. What I need then is to show month by month the sum of the [Rate price] when [Rate description] = fixed AND the [Rate price]*[Avg usage] when [Rate description] = variable. I want to show this revenue until the last [Contract end date].

Unfortunately I cannot share the file because it has sensitive information =/

[benvatvandata]

How do you want this grouped? Just by Month, and show summary for each Month? 

Also, do you have a standard Date field? Or do you only have Contract begin/end dates?

For example, 2 columns (assuming for 1 customer, [Contract begin date] = 01/01/2020, [Contract end date] = 06/30/2020, [Rate Price] = $100 (for fixed rate)... I'm excluding the variable rate just for this example, but it shouldn't be hard to include that once I understand what you're looking for):

Month    |   Revenue

Jan          |   $100

Feb          |   $100

etc...

Jun           | $100

It's fine if you can't share a file, but could you possibly create some sort of example with fake numbers? A spreadsheet of your desired output, and fake dates/values describing the contract start/end dates and rate prices would be extremely helpful.

Thanks,

Ben

[gcorazza]

@benvatvandata , it's basically that! I've attached this excel example as requested. 

[Brett_Bleess]

I just want to point out the Settings\Document Properties\Scrambling tab where you can scramble any field data that is sensitive, names etc., so that should potentially allow you to attach the QVW file once you scramble the things about which you are concerned, which I think will likely help get some furthe responses.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Document_Proper...

Regards,
Brett