1 2 Previous Next 19 Replies Latest reply on Jul 25, 2017 3:55 PM by Okechukwu Ossai

# I want to see last months 1st 7 days of records

I have a case where I want to look at the previous months 1st 7 days based on the current months 1st 7 days.

For example, the current months 1st 7 days we have some records for each day. So I want to look at what the records are for the previous months 1st 7 days.

Thanks

Ankit

• ###### 1. Re: I want to see last months 1st 7 days of records

Not sure I got it right ... but what if you bring date time dimension into filter as discrete days (select 1-7) and then visualise per month or perhaps add month(date time) into colour and visualise a comparison of those 7 days across months.

• ###### 2. Re: I want to see last months 1st 7 days of records

I want it as a calculated field

• ###### 3. Re: I want to see last months 1st 7 days of records

IF DATEPART('day',DateTime)<7 then .... whatever you want

• ###### 4. Re: I want to see last months 1st 7 days of records

Hi Ankit,

I hope I understood what your question is. If your current month will always have data for each day of the first 7 days, then a simple DATEPART() function can be used to display the first 7 days data of the previous month. The calculated field will not be based on the current month.

However, if there is any chance that the current or previous month may have missing records in the first 7 days then an alternative approach is required.

I have adopted the second scenario in my solution. This will handle all potential data cases.

See solution below using a sample dataset.

Step 1: Create calculated field [Weekday]

DATEPART('day', [Order Date])

Right click on the field name and select "Convert to Dimension"

Step 2: Create calculated field [First 7 Days]

IF [Weekday] <= 7 THEN "Yes" ELSE "No" END

Step 3: Create calculated field [Include Date?]

IF [Order Date] > DATE(DATEADD('month', -1, DATETRUNC('month', TODAY())))-1 THEN "Include" ELSE "Exclude" END

Step 4: Create calculated field [Include Date Count]

{FIXED YEAR([Order Date]), [First 7 Days], [Include Date?], [Weekday]:

COUNTD(IF YEAR([Order Date])=YEAR(TODAY()) AND [First 7 Days] = "Yes" AND [Include Date?] = "Include" THEN [Order Date] END)}

This will count the number of records per weekday for the first 7 weekdays in the previous and current months. A count of 2 means there are records for that weekday in both previous and current months.

Step 5: Create calculated field [Last Month 1st 7 Days]

YEAR([Order Date]) = YEAR(TODAY()) AND MONTH([Order Date]) = MONTH(TODAY())-1 AND [Include Date Count] = 2

Put this on the filter shelf and set to True.

Hope this helps.

Ossai

• ###### 5. Re: I want to see last months 1st 7 days of records

Hi, Ankit

Please find my solution attached. Okechukwu Ossai Thanks for your workbook, I used your workbook but with another solution. In your solution, I think the logic for Year([Order Date]) = Year (Today()) should be removed as in case it is January as current month.

Below is a sample of result.

If today has less than 7 days data

Hope this could help

ZZ

1 of 1 people found this helpful
• ###### 6. Re: I want to see last months 1st 7 days of records

Well spotted Zhouyi Zhang.

Hi Ankit,

Is your data always going to be current? If yes, referencing TODAY() in the formulas will be okay. However, if the database may become outdated at any time, it will be good to use MAX(Date) instead. I've modified the calculated fields to account for the end of year scenario mentioned by Zhouyi and also for when the database may be  out of date. See attached workbooks. You can replace <=7 with <7 if you don't want to include the seventh day. I've assumed that you want to see the first 7 days of records (1st week) and not first 7 records (may cut across several weeks). Is this correct? The Weekday formula can be easily modified if you prefer the first 7 records instead.

Step 1: [Weekday]

DATEPART('day', [Order Date])

Right click on the field name and select "Convert to Dimension"

Step 2: [First 7 Days]

IF [Weekday] <= 7 THEN "Yes" ELSE "No" END

Replace <= with < if you don't want to include the seventh day.

Step 3: [Include Date]

DATE(IF DATE(DATETRUNC('month', [Order Date])) =  DATE(DATEADD('month', -1, DATETRUNC('month', {FIXED:MAX([Order Date])}))) THEN DATETRUNC('month', {FIXED:MAX([Order Date])})

ELSE DATE(DATETRUNC('month', [Order Date])) END)

Step 4: [Include Date Count]

{FIXED [Include Date],[First 7 Days], [Weekday]: COUNTD(IF [Include Date] = {FIXED: MAX([Include Date])} AND [First 7 Days] = "Yes" THEN [Order Date] END)}

This will count the number of records per weekday for the first 7 weekdays in the previous and current months. A count of 2 means there are records for that weekday in both previous and current months.

Step 5: [Last Month 1st 7 Days]

DATETRUNC('month', [Order Date]) = DATE(DATEADD('month', -1, DATETRUNC('month', {FIXED:MAX([Order Date])}))) AND [Include Date Count] = 2

Put this on the filter shelf and set to True.

Hope this helps.

Ossai

• ###### 7. Re: I want to see last months 1st 7 days of records

Hey Ossai,

Thanks for the reply, I would like to see this months values, along with the previous months values so that I can compare them and make some decisions based on them. Also I would like to include weekend dates too and not just limit to weekdays dates.

ThanksAnkit

• ###### 9. Re: I want to see last months 1st 7 days of records

Hi Ankit,

[Weekdays] is just a name I gave the calculated field. It includes both week days and weekend dates.

It lists the first 7 days in a week.

Ossai

• ###### 10. Re: I want to see last months 1st 7 days of records

Okay, so using this how can I put a view where I have "This months" date in order date and for measures I have "this months" sales and "previous months" sales.

I hope you get what I am trying to show here in the view.  I apologize for not explaining this clearly, please let me know if you didn't get this.

Thanks
Ankit

• ###### 11. Re: I want to see last months 1st 7 days of records

Is this similar to the second screenshot (Common First 7 days data) in my previous response?

• ###### 12. Re: I want to see last months 1st 7 days of records

In the view I would like to see in the order date: 07/01,07/02...07/07 and for the measures I want to look at the sales(current_7days) and sales(prev_7days).

• ###### 13. Re: I want to see last months 1st 7 days of records

Hey Ossai,

I hope you got what I am trying to achieve in the view.

• ###### 14. Re: I want to see last months 1st 7 days of records

Yes, so you want to place the sales data side by side.

That will be tricky to achieve because the data are from different rows in the database. I'd check if this is possible.

1 2 Previous Next