Category
Report Builder
Product Version
9.0
Compare sum of revenue with dynamic dates
Answered
Hello,
I would like to calculate the sum of the revenue of this month until today and compare it with that of the previous month until the same day and that the result increases every day. Here is an example to make it easier to understand.
Example:
sum of last month's revenue (from June 1st to June 27th)
sum of this month's revenue (from July 1st to July 27th)
The sum is accumulated every day. Then the next day it would look like this:
sum of last month's revenue (from June 1st to June 28th)
sum of this month's revenue (from July 1st to July 28th)
And so on...
As a result I would like to receive one number per month.
Is this possible?
Thanks for your help in advance.
Best regards
DataOps Team
The same question
Yes this is possible using an append subquery.
Create your master report
Add a date filter, use Define Value -> Predefined Period and select "This Month To Date". Ski team example below has old dates in it so I can't actually do it as instructed
Create a calculated field called "Join" or any name really. For the field just enter 1 and add. Save
Add it to your report and hide the field
Add a subquery, type Append, basic
On the next screen add another calculated field, called it whatever and also have it so it just has 1
Join the master and subquery using the calculated fields
In your subquery add the same date filter as your master query and basically add whatever metrics you want
Go into filter advanced settings and link the subquery date filter to the master date filter but give it an offset of - 1 MONTH
When you run the report you should now have 2 fields with one being the same dates but one month earlier
Yes this is possible using an append subquery.
Create your master report
Add a date filter, use Define Value -> Predefined Period and select "This Month To Date". Ski team example below has old dates in it so I can't actually do it as instructed
Create a calculated field called "Join" or any name really. For the field just enter 1 and add. Save
Add it to your report and hide the field
Add a subquery, type Append, basic
On the next screen add another calculated field, called it whatever and also have it so it just has 1
Join the master and subquery using the calculated fields
In your subquery add the same date filter as your master query and basically add whatever metrics you want
Go into filter advanced settings and link the subquery date filter to the master date filter but give it an offset of - 1 MONTH
When you run the report you should now have 2 fields with one being the same dates but one month earlier
Hi DataOps Team,
Thanks for reaching out to support. This type of content creation question would be out of scope for Support but I'll leave this open to community input.
Thanks,
Eric
Hi DataOps Team,
Thanks for reaching out to support. This type of content creation question would be out of scope for Support but I'll leave this open to community input.
Thanks,
Eric
Yes this is possible using an append subquery.
Create your master report
Add a date filter, use Define Value -> Predefined Period and select "This Month To Date". Ski team example below has old dates in it so I can't actually do it as instructed
Create a calculated field called "Join" or any name really. For the field just enter 1 and add. Save
Add it to your report and hide the field
Add a subquery, type Append, basic
On the next screen add another calculated field, called it whatever and also have it so it just has 1
Join the master and subquery using the calculated fields
In your subquery add the same date filter as your master query and basically add whatever metrics you want
Go into filter advanced settings and link the subquery date filter to the master date filter but give it an offset of - 1 MONTH
When you run the report you should now have 2 fields with one being the same dates but one month earlier
Yes this is possible using an append subquery.
Create your master report
Add a date filter, use Define Value -> Predefined Period and select "This Month To Date". Ski team example below has old dates in it so I can't actually do it as instructed
Create a calculated field called "Join" or any name really. For the field just enter 1 and add. Save
Add it to your report and hide the field
Add a subquery, type Append, basic
On the next screen add another calculated field, called it whatever and also have it so it just has 1
Join the master and subquery using the calculated fields
In your subquery add the same date filter as your master query and basically add whatever metrics you want
Go into filter advanced settings and link the subquery date filter to the master date filter but give it an offset of - 1 MONTH
When you run the report you should now have 2 fields with one being the same dates but one month earlier
Thanks for the input Dean! Always appreciated.
I'll mark this Question as answered at this time, feel welcome to reach out in the future.
Thanks,
Eric
Thanks for the input Dean! Always appreciated.
I'll mark this Question as answered at this time, feel welcome to reach out in the future.
Thanks,
Eric
Hello,
I am sorry to open this question again, but another question has come up:
Is it now possible to insert a field where the ratio is calculated from both values?
By the two values I mean it would be something like this:
ratio = revenue this month to date / revenue this month to date - 1 month
It would be like calculating a growth in turnover on a daily basis.
Thank you!
Hello,
I am sorry to open this question again, but another question has come up:
Is it now possible to insert a field where the ratio is calculated from both values?
By the two values I mean it would be something like this:
ratio = revenue this month to date / revenue this month to date - 1 month
It would be like calculating a growth in turnover on a daily basis.
Thank you!
Hi Guys,
You may be able to use parameters for this type of output, a field might be tricky, but I think you could do this pretty easily with a text widget at least, see here -
https://wiki.yellowfinbi.com/display/yfcurrent/Text+Widget+Parameters
You may also be able to do this in code mode.
Hope this helps! I'll keep this open for community input for now.
Thanks,
Eric
Hi Guys,
You may be able to use parameters for this type of output, a field might be tricky, but I think you could do this pretty easily with a text widget at least, see here -
https://wiki.yellowfinbi.com/display/yfcurrent/Text+Widget+Parameters
You may also be able to do this in code mode.
Hope this helps! I'll keep this open for community input for now.
Thanks,
Eric
Hello Eric,
Thank you very much for your reply :)
I will try to do it with widgets.
Thanks for leaving this open, in case someone has more ideas.
Have a nice day!
Hello Eric,
Thank you very much for your reply :)
I will try to do it with widgets.
Thanks for leaving this open, in case someone has more ideas.
Have a nice day!
Hi Guys,
Just wanted to check in to see how things went here.
Thanks,
Eric
Hi Guys,
Just wanted to check in to see how things went here.
Thanks,
Eric
Hi Eric,
I have not really been able to check if the ratio calculation works that way, sorry.
Hi Eric,
I have not really been able to check if the ratio calculation works that way, sorry.
Hi Guys,
Thanks for the update. If you need additional assistance creating this type of content, feel welcome to reach out to an account manager as this would be out of scope for support.
Thanks,
Eric
Hi Guys,
Thanks for the update. If you need additional assistance creating this type of content, feel welcome to reach out to an account manager as this would be out of scope for support.
Thanks,
Eric
Replies have been locked on this page!