Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gcorazza
Contributor
Contributor

Monthly revenue forecast from monthly fee

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 =/

3 Replies
benvatvandata
Partner
Partner

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
Contributor
Contributor
Author

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

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.