Compare sum of revenue with dynamic dates

DataOps shared this question 20 months ago
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

Best Answer
photo

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

b4e15c6a8eb73dfdefed87dc285d06db

54cc1fffea7621d6789f11de70958b16

Create a calculated field called "Join" or any name really. For the field just enter 1 and add. Save

a6d387c14533862a38c17e1c6fa75f30

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

3b0ba9b711e1fa8c8f0e8afca2b5e351

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

ac3c1517092481c15fa875ea21f20334


When you run the report you should now have 2 fields with one being the same dates but one month earlier

1250554f9da923a000530d4c2218cff6

Replies (9)

photo
1

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

photo
1

Thanks Eric to you as well :)

photo
photo
2

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

b4e15c6a8eb73dfdefed87dc285d06db

54cc1fffea7621d6789f11de70958b16

Create a calculated field called "Join" or any name really. For the field just enter 1 and add. Save

a6d387c14533862a38c17e1c6fa75f30

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

3b0ba9b711e1fa8c8f0e8afca2b5e351

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

ac3c1517092481c15fa875ea21f20334


When you run the report you should now have 2 fields with one being the same dates but one month earlier

1250554f9da923a000530d4c2218cff6

photo
2

Thank you very much, Dean!!

I have tried it and it works, wonderful! :-D

photo
photo
1

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

photo
1

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!

photo
1

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

photo
1

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!

photo
1

Hi Guys,

Just wanted to check in to see how things went here.

Thanks,

Eric

photo
1

Hi Eric,

I have not really been able to check if the ratio calculation works that way, sorry.

photo
1

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

Leave a Comment
 
Attach a file