7 Replies Latest reply on Oct 9, 2018 3:10 PM by NAYANA MADHEKAR

# Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

Hi I have sales for different Markets A, B & C. And sales for them respectively on specific dates.

I could easily have sales for current week, present month (Week ending dates of current month) & Quarterly (specific dates) on different sheets using filters.

But I wish to have them on a specific dashboard, with no use of filters as the report is suppose to be dynamic.

Can you please help with a way to do this? Would parameters help? What function would help?

PFA.

• ###### 1. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

Here's how you do it all dynamically, not need for parameters.  Just use a lot of date functions

In the attachment:

TW = This Week

LW = Last Week

TM = This Month

LM = Last Month

TQ = This Quarter

I didn't do LQ because there wasn't any data, but you would just set the filter to 3-5 (remember that the quarter calc is zero based so the 3 months in the quarter are 0,1,2.

If your quarter is not "the last 3 months" then you just need to write more code to calculate it better.

• ###### 2. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

Thank you so much Chris for your help. It really encourages people like us to not give up & keep trying.

This helps, but as u see you have different sheets for TW, LW, TM, LM. I want all these views in one Sheet.

The report demands to have these as separate columns and not in separate sheets. (For better comparison of data).

Any thoughts on that?

• ###### 3. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

Also, my quarter is from 3.31 to 6.30, would the code come in Month diff?

• ###### 4. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

Hi @Chris, should I expect to see you at TC18?

• ###### 5. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

It would be tricky to do it on one sheet, you would probably have to create a lot of LODs and other calculations.

Why does it "must" be in 1 sheet ? Why not a dashboard ?

• ###### 6. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

I'll be there    make sure you stop me and say Hi, but I'm probably running off to somewhere else as well.

• ###### 7. Re: Add Last Week Sales, Current month to Date Sales, & Quarterly Sales on one Sheet/View (For Dynamic Dates)

It would be a pleasure! Thank you. Hope to see you soon!