7 Replies Latest reply on Jan 14, 2020 8:01 PM by koushik.neelakantha

# RM  vs Sales in same sheet

Hello everyone, Please find the attached workbook.

I have rolling month sales and  sales field in the same sheet.

My requirement is : Rolling month sales should be irrespective of  year and month filters where as  sales field should get change when we change year and month filter.

Rolling Month sales and direct sales should be in same sheet.

As per the attached workbook, Rolling month sales should be 631 ( Feb 19 to Jan 20) and sales column should get  changed as per filters selection.

Note: we can create a seperate sheet for direct sales field and rolling months, but as per the user requirement i need to keep both the fields in same sheet as i have space constraint and lot of fields.

Regards,

Koushik.

Mahfooj Khan

Zhouyi Zhang

Soumitra Godbole

• ###### 1. Re: RM  vs Sales in same sheet

Hi Koushik,

I have come up with a solution but am still not perfectly clear what exactly are you trying to do.

The trick was to create a new date field by adding 1 month to the current month field

which has been renamed to Date (to avoid confusion).

Date New

Rolling Months

{Fixed [Year New]:Sum(If [Year New]>= 2019 Then [sales] END)}

In the above visual, year = 2019 and month = January gives Rolling Sum =  ____ (Null)

For Feb 2019 to Dec 2019, Rolling Sum = 631 and for Jan 2020 also Rolling Sum = 631

Let me now if this looks right.

What sounds weird is that the Rolling Sum of Sales is independent of Year and Month.

In case of 2019, I understand you will have a total of 631. The question is what are we

supposed to get when we select 2020 ? I feel that the Running sum should have some

dependence on the Year.

Please confirm whether this makes sense or not. Also I have attached a twbx file for your

reference. Best Wishes !

Sincerely,

Soumitra

• ###### 2. Re: RM  vs Sales in same sheet

Hello Soumitra Godbole Thanks a for the reply.

For rolling month sales : we are in Jan 2020, rolling month sales should appear from feb 19 till Jan 20. It should automatically change every month.

for example: if we are in Mar 20. rolling month should be from april 19 till mar 20.

Note: even though we change date filters, rolling month sales should be static as it automatically change with rolling months logic (DATEDIFF or DATE ADD)

Sales: This column should change with year and month filters.

NO HARDCODING OF VALUES AND BOTH FIELDS SHOULD BE IN SAME SHEET.

Regards,

Koushik.

1 of 1 people found this helpful
• ###### 3. Re: RM  vs Sales in same sheet

Hi Koushik,

Thanks for the explanation. Here is my modified solution:

Calculated Fields:

It required the following 3 calculated fields (using LOD and Today() function)

Building the Visual:

Simply place the above fields in their respective shelves as shown below

Hope this explanation was useful and made sense. Please note that the Rolling Months is fixed

within a given period (in this case from Feb-19 to Jan-20) so as long as the year month selection

is within this range it should work fine.

If we go out of range for eg:- we select Mar-2020, when we are currently in Jan-2020, the Rolling

Months will be blank. This is based on my understanding of your explanation.

To see future dates, instead of using the Today() function, you will need to create a Parameter for

[Year] and Parameter for [Month]. For your reference, I have attached the twbx file. Let me know if

you have any questions or need any modification. Thanks !

Sincerely,

Soumitra

• ###### 4. Re: RM  vs Sales in same sheet

Hello again,

Not so fast ! I challenge my earlier solution as it looks a bit retarded. Here is a

really cool solution that is way more simpler than the earlier solution. Let me know

Here we directly use values from your filters (Month & Year) to change the values of the Rolling Month

and Monthly Sales (instead of using Today() as in the earlier solution). Also we do not even need to use

any Parameters.

That's all folks. Also in addition to your dashboard, I wanted to visualize how the Monthly

Sales would vary in comparison to the Rolling Months Sales which would be similar to

the Greatest Integer Function [  ] and appear as a Staircase or Step chart.

Anyway, I have attached the twbx file for the above solution and hope this  was cool and helpful.

Best Wishes !

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 5. Re: RM  vs Sales in same sheet

Hi soumitra, Thanks a lot for the reply and sharing workbook.

Please find the attached work book and excel file.

I have used below logic for Rolling months so that when i try to change year and month filters, Rolling months is static and sales YTD is getting changed.

{fixed :SUM(if DATEDIFF('month',[month],TODAY())<12 and DATEDIFF('month',[month],TODAY())>=0 then

([sales])

END)}

My query is when we enter Feb month, will my Rolling months give me 620 automatically ( Feb 20 to march 19) ??

Expected output should be :

1) even though when we select month and year filters ,Rolling months column should be static. It should change when we enter new month automatically.

Example: when we enter next month ie, Feb 2020, rolling months field should show 620 automatically.

2) Sales YTD should change with month and year filters.

3) Rolling months and Sales YTD should be present in same sheet

Regards,

Koushik.

• ###### 6. Re: RM  vs Sales in same sheet

Hi Koushik,

You just lost me with your last example using Feb 20 to Mar 19. Shouldn't it

be Mar 19  to Feb 20 as per your earlier explanation. Your method shown makes

sense but it will give you the sum total for 2019 & 2020. What we need is to have

a field that separates the 12 month periods (Feb to Jan) ie

Jan19,

Feb 19 to Jan 20,

Feb 20 to Dec 20.

That was the tricky part and so I came up with the idea of creating a field Date which

would be 1 month behind the month field. This way Feb 19 to Jan 20  in the Month field

would mean Jan 19  to Dec 19 in the Date field. This also simplifies the LOD calculation.

What my 2nd equation does is it simplifies the whole calculation. Here is what it would

appear if done in Excel. I have also shown the Date (-1 month) field below to give you

an idea

I did confirm I am getting the above numbers in both my Tableau solutions.

The only difference being is that in my first solution, I have used Today

which means until we reach 1st Feb 2020, the Rolling Month value will

be 631 and on 1st Feb, it will change to 716.

Please let me know if this makes sense and solves your problem.

Sincerely,

Soumitra

1 of 1 people found this helpful
• ###### 7. Re: RM  vs Sales in same sheet

Hi soumitra,

Using LOD solved the issue thanks a lot for providing the logic.

will implement the same in real time and check.

Regards,

Koushik.