12 Replies Latest reply on Feb 22, 2018 4:34 AM by rakhesh.nellikoppa

# Quarter Sales Question  - Need community help

Hi Community@@@@@@@,

I am looking quarter level sales but based on some logic : if I select one date then  --- selected date Sales + Prev month Latest Date + Prior to Prev Month Latest Date Sales after that need to aggregate at quarter level. If my current month is Feb then Selected date Sales + Prev month Latest Date Sales (Jan) at Product Level.

For Dec - selected Prod Sales + Prev month Latest date  + Prior to Prev Latest date sales then aggregate at quarter level.

Please check the expected result column, that's my expected result

I have tried to do this using LOD but didn't achieved the expected result, attached the sample workbook for your reference, please look into this.

Thank you.

Regards,

Rakesh

 Date Quarter Product Sales Expected Result 09-02-2018 Q1 A 5 13 01-02-2018 Q1 A 10 18 15-01-2018 Q1 A 8 8 10-01-2018 Q1 A 2 2 12-12-2017 Q4 A 15 15 11-12-2017 Q4 A 20 60 11-11-2017 Q4 A 15 40 11-10-2017 Q4 A 25 25 09-02-2018 Q1 B 23 32 01-02-2018 Q1 B 10 19 18-02-2018 Q1 B 5 14 10-01-2018 Q1 B 9 9 12-12-2017 Q4 B 15 49 31-12-2017 Q4 B 20 54 21-11-2017 Q4 B 19 34 28-10-2017 Q4 B 15 15
• ###### 1. Re: Quarter Sales Question  - Need community help

Hi,

Thank you.

Regards,

Rakesh

• ###### 2. Re: Quarter Sales Question  - Need community help

hi Rakhesh,

So can you give me a couple of examples of the expected figure, and where it comes from

If I look at Product A

It looks like the Expected Result for the 11/12/2017 is Current Day's Sales (11/12/107 = 20) + Previous Months Sales (11/11/2017 = 15) + Month Before That (11/10/2017 = 25) = 60. But why does the expected value for 12/12/2017 = 15?

And can't work out why the result for 09/02/2018 = 13

If you can detail out the examples for December and February (with the 2 or 3 values which make up the Expected)...similar to how I have above. I'll take a look

• ###### 3. Re: Quarter Sales Question  - Need community help

Hi Simon Runc,

Thanks for looking into this, Yes you are right for 11th Dec 2017 expected figure is 60, I corrected now and shown with excel formula as below.

Formula for  :

Current Day Sales + Prev Month Latest Day Sales + Month Before that Latest day sales .

Thanks again checking and let me know if you need any more information.

Regards,

Rakesh

• ###### 4. Re: Quarter Sales Question  - Need community help

Thanks for that...So just to double check I've understood correctly

It's the Current Days Sale + The Last DAY of the Previous Month (and so not the entire last month) + The Last DAY from the Month before (again, not the entire month)?

And you want to be able to select a single date, and then have that information shown for the selected Month? If so, I don't think we'll be able to use LoDs as we need to move back through the months dynamically (eg. if Feb is selected we need to get data from Jan and Dec, but if March is selected we need to get data from Feb and March). The easiest way would be to use a parameter to select the date, can you let me know if this is an option? If not, then we are (probably) going to have to use a Table Calculation Filter, in which case we can't apply this across worksheets, so can you let me know if this will be a stand-alone sheet, or if you plan to use in a multi-sheet dashboard, where the selection of the date would need to reflected in multiple sheets.

Thanks

• ###### 5. Re: Quarter Sales Question  - Need community help

It's the Current Days Sale + The Last DAY of the Previous Month (and so not the entire last month) + The Last DAY from the Month before  ( at Product Level and Quarter Level Aggregation )

As of now we can show the result as cross tab with all the dates, we don't select any dates and will show all the dates information to make it simple. Use your easy method (LOD) and write the logic.

Ex :  ((Selected mean particular row ))

For March we should count Selected Date + Feb Latest Day Sales + Jan Latest Day Sales

For Jan we should count only Jan month selected Sales

For Nov We should count Selected date sales + Oct latest day sales

let me know if any confusion/or i am making it complex to explain.

Thanks again.

Regards,

Rakesh

• ###### 6. Re: Quarter Sales Question  - Need community help

So I don't think there is an LoD solution, as we need to move up through the Partitions (Months), and so need self-referencing calculations...ie. Table Calculations.

This is pretty tricky to do, as Tableau don't think like Excel (cell based Excel vs Set Based Tableau and Databases) so can be tricky to move up and down "cells". I think I've found  a way, so if you can check a few of the "Expected Values" and if all is working as you expect, I'll try and explain how it works!

1 of 1 people found this helpful
• ###### 7. Re: Quarter Sales Question  - Need community help

Thanks Simon Runc

I will check and update you. you are always awesome and helpful.

Thanks again.

Regards,

Rakhesh

• ###### 8. Re: Quarter Sales Question  - Need community help

Thanks a lot Simon Runc.

Your logic works superbly, but need sometime to understand the behavior as its more on Table calculation. I am doing reverse engineering and slowly understanding, will get back to you if required any more clarification.

As of now i am good and thanks again for your timely help.

Regards,

Rakesh

• ###### 9. Re: Quarter Sales Question  - Need community help

So yes it's pretty complicated!

The LoD [Last Date of Each Month] just gets the last entry of each month, for each product

and then [Sales Last Day of Month] uses this to only populate the Sales for that row.

[Sales Previous Month]

LOOKUP(SUM([Sales Last Day of Month]),-1)

Gets the sales of the last day of the month from the month before, with this set up (and in this order so month above date...you can drag and drop to re-order)

However where there are 2 entries in a month, the second one (being more than 1 partition away) doesn't get the sales (of last day) of previous month, so we use this formula to populate that row

[Sales Previous Month - Populated]

IF ISNULL([Sales Previous Month]) THEN PREVIOUS_VALUE([Sales Previous Month])

ELSE [Sales Previous Month]

END

and is set up like this...Notice the restarting every month

and then the month before is the same, but the lookup is -2

Then we can just add them all together...hope that helps get you started on the reverse engineering

1 of 1 people found this helpful
• ###### 10. Re: Quarter Sales Question  - Need community help

Hi Simon Runc,

Thanks for the detailed explanation.

This is working fine with small data set, but when i changed to more data. Its looking weird.

For Oct and Nov month (First 2 months of quarter) its working fine however its not working as expected for the last month of the quarter where we add prev + month before. Prev month value is fine but Month before its not taking properly. in the attached example for Dec month its not working as expected and its taking month before only for last day of Dec.

Attachment Name : Specific Previous Rules_issue_v1 (Attached check my post top )

Am i  missing something ?

Thank you

- Rakesh

• ###### 11. Re: Quarter Sales Question  - Need community help

hi Rakhesh,

Yes I think I know the problem...The previous solution happened to work with that dataset, but hadn't foreseen how it would behave with different data (it can be like these with these Excel type formulas, there can be a bit of playing to ensure it works for all situations)

The problem was that, depending, on the number of data-days in each month, it wasn't always going back and getting the Month Before properly, which only existed in a single row (the last day of the month), so I've made the following change

I've added this formula, to populate the last day of the month figure to every row that month

[Sales Last Day of Month - LoD to Every Day]

{FIXED [Product], DATETRUNC('month',[Date]): SUM(IIF([Last Date of Each Month],[Sales],NULL))}

and then used this field in stead of [Sales Last Day of Month] in the other formulas

such as

[Sales Previous Month]

LOOKUP(SUM([Sales Last Day of Month - LoD to Every Day]),-1)

Hopefully that does the trick!

1 of 1 people found this helpful
• ###### 12. Re: Quarter Sales Question  - Need community help

Thanks a lot Simon Runc.

This is working exactly the way i was looking, and thanks for quick turnaround.

You are amazing and so helpful.

Thanks again and have a good day.

Regards,

Rakesh