Calculating master calendar days

[jworthulf]

Hi Guys,

I have a master Calendar setup on my dashboard

I need to find the number of  days (Monday - Sunday) currently selected on the master calendar in order to use it as part of an expression. 

LOAD

  WeekDay([SHIP DT]) as DOW,

     [SHIP DT],

     [SHIP DT] as  ship_dt,

     fiscal_year,

     fiscal_week,

     fiscal_month,

     fiscal_qtr,

     monthyr,

     quarteryr,

     weekyr,

     budget_wk

[johnca]

Okay, if you are joining sales data by SalesDate to the master calendar MASTER_DATE then the solution should be simple. Create a new field in the sales data SalesDate as MASTER_DATE to join by. Then, by selecting the SalesDate and calculating the difference between the max(MASTER_DATE) and Min(MASTER_DATE) you should get the correct value. If you want to include both min and max dates add 1. What does DOW have to do with it?

[jworthulf]

John,

Thanks for the help and the example much appreciated

[jworthulf]

Youssef,

Thanks for the help your response was correct as well.

[jworthulf]

John,

When I select week 15 in your example the output should be 7 but it shows up as 6 why is that?

[johnca]

JW,

It is simply subtracting the min date (1/2/2017) from the max date (1/8/2017). Did you need days inclusively? Add 1...

(max(MASTER_DATE) - Min(MASTER_DATE)) + 1

[jworthulf]

Ok that worked ....thanks again.

[johnca]

A better solution might be the following set;

=count({<MASTER_DATE={"<=$(=Max(MASTER_DATE))>=$(=Min(MASTER_DATE))"}>}DOW).

If this question has been answered please close it by selecting the answer and designating it as such.

[jworthulf]

John

I tried using your update but unfortunately it didn't work.    I ended up using the following based on the feedback I received from you & Youssef.

COUNT({$<VENDOR=,Units=,BUYER=,ItemID=>}TOTAL MASTER_DATE+1)

Thanks again for all your help.

Happy Holidays,

JW