Skip to Content

Problem Statement:

We often get a requirement to generate a report based on a given date range, a typical requirement would be to display charts based on the date range selected. if user selects date range as too big then the chart will clutter with so many points and impacts chart readability.

Do we have any option to change the chart axis dynamically based on the date range.

For example –


If user selects data range less than a month then show week wise data

If selection is more than a week and less than a month then show month wise data

If selection is more than a month and less than a quarter then show month wise data

If selection is more than a quarter and less than a year then show quarter wise data

If selection is more than a year and less then show year wise data

Solution:


This is how we could approach the solution, create a variable which gives the output as Date, Week, Month, Quarter and Year based on the Number of Days between the date range selected by the user, use this variable while creating the chart.

Below are the detailed steps for creating variables and chart.

Step1 : Create Report:

Create a sample report with Island Resorts marketing Universe with objects like Country, Invoice Date and Revenue

Create Range Filter on Invoice date as with prompt text as “Start Date” and “End Date” respectively.

Make these prompts as optional so that user can run the report fir entire data range

Query will look like below

SELECT

Resort_Country.country, Sales.invoice_date, sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price)

FROM

Country  Resort_Country,

Sales,  Invoice_Line,  Service, Service_Line,  Resort

WHERE

( Resort_Country.country_id=Resort.country_id  )

AND  ( Sales.inv_id=Invoice_Line.inv_id  )

AND  ( Invoice_Line.service_id=Service.service_id )

AND  ( Resort.resort_id=Service_Line.resort_id )

AND  ( Service.sl_id=Service_Line.sl_id  )

GROUP BY

Resort_Country.country,

Sales.invoice_date

Step 2: Create these variables in the report level


1. User Start Date

=If(IsPromptAnswered(“Start Date”) ;ToDate(UserResponse(“Start Date”);”INPUT_DATE_TIME”))

2. User End Date

=If(IsPromptAnswered(“End Date”) ;ToDate(UserResponse(“End Date”);”INPUT_DATE_TIME”))

3. No of Days

=DaysBetween([User Start Date];[User End Date])

4. Year

=FormatNumber(Year([Invoice Date]);”####”)

5. Year_Number(YYYY)

=Year([Invoice Date])

6. MonthYear(Mon-YYYY)

=Left(Month([Invoice Date]);3)+” – “+[Year]

7. Month_Number(YYYYMM)

=[Year_Number]*100+MonthNumberOfYear(([Invoice Date]))

8. Quarter(YYYY-QQ)

=[Year]+”-Q”+Quarter([Invoice Date])

9. Quarter_Number(YYYYQ)

=[Year_Number]*10+Quarter([Invoice Date])

10. Date_Number(DDMMYYYY)

=(DayNumberOfMonth([Invoice Date])*100+MonthNumberOfYear([Invoice Date]))*10000+[Year_Number]

11.Week

               =”w”+Week([Invoice Date])+”-“+[MonthYear(Mon-YYYY)]

12.WeekNumber

               =Week([Invoice Date])*1000000+[Month_Number(YYYYMM)]

13. Dynamic_Period

=If([NoofDays]<=7;[Invoice Date];If([NoofDays]<=31;[Week];If([NoofDays]<=90;[MonthYear(Mon-YYYY)];If([NoofDays]>90 And [NoofDays] <=365;[Quarter(YYYY-QQ)];[Year]))))

14. Dynamic_Period_Number

=If([NoofDays]<=7;[Date_Number(DDMMYYYY)];If([NoofDays]<=31;[WeekNumber];If([NoofDays]<=90;[Month_Number(YYYYMM)];If([NoofDays]>90 And [NoofDays] <=360 ;[Quarter_Number(YYYYQ)]; [Year_Number]))))

 

Output of above variables will be like below

Dynamic
_Period

User
Start
Date

User End
Date

Noof
Days

Invoice
Date

Date_Nu
mber
(DDMMY
YYY)

Week

WeekNu
mber

MonthYear
(Mon-
YYYY)

Month_N
umber
(YYYYM
M)

Quarter
(YYYY-
QQ)

Quarter_Nu
mber
(YYYYQ)

Year

Year_Number

1,199,801

1/1/98

1/31/98

30

1/1/98

1,011,998

w1-Jan – 1998

1,199,801

Jan – 1998

199,801

1998-Q1

19,981

1998

1,998

1,199,801

1/1/98

1/31/98

30

1/2/98

2,011,998

w1-Jan – 1998

1,199,801

Jan – 1998

199,801

1998-Q1

19,981

1998

1,998

2,199,801

1/1/98

1/31/98

30

1/6/98

6,011,998

w2-Jan – 1998

2,199,801

Jan – 1998

199,801

1998-Q1

19,981

1998

1,998

2,199,801

1/1/98

1/31/98

30

1/7/98

7,011,998

w2-Jan – 1998

2,199,801

Jan – 1998

199,801

1998-Q1

19,981

1998

1,998

2,199,801

1/1/98

1/31/98

30

1/11/98

11,011,998

w2-Jan – 1998

2,199,801

Jan – 1998

199,801

1998-Q1

19,981

1998

1,998

3,199,801

1/1/98

1/31/98

30

1/12/98

12,011,998

w3-Jan – 1998

3,199,801

Jan – 1998

199,801

1998-Q1

19,981

1998

1,998



Step 3: Create Chart using Country, Dynamic_Period,Dynamic_period_Number,Revenue objects

Hiding Number Column.png

Step 4: Sort the chart on Dynamic_Period_Number variable

Sorting Number.png

note:

Here i have hidden the Dynamic period Number variable in the chart and sorted on it, but display variable will be Dynamic Period. This to avoid the sorting issue which you get while using the Dynamic Period Object and the explanation of the issue is at the end of this solution.


Step 5: Create chart using Country, Invoice_Date and Revenue object


Step 6: Report output with different set of date parameters


1) Date Range: Entire Date Range

No value selected for Start and End date (left them blank) as they are optional

Report output with normal date as the x-axis value

1. Default Date wise chart.jpg

Report output with Dynamic Period as the x-axis value shows year wise data, as the entire data is for 3 Years in the data base

2 Dynamic Period Chart_optional Prompt.png

2) Date Range:   One week

                                 Start Date: 1/1/1998

                                 End date: 7/1/1998

Report output with normal date as the x-axis value

13. Default Chart for 7 Days of Data.png

Dynamic Period Chart gives day wise data

14. Dynamic Chart for 7 Days of Data.png

3) Date Range:   One Month of Data

                                  Start Date: 1/1/1998

                                 End date: 31/1/1998




Report output with normal date as the x-axis value

11. Default hart with One Week Of Data.png

Report output with Dynamic Period as the x-axis value shows week wise data

12. Dynamimc chart with One Week Of Data.png

4) Date Range:   Three Months of Data

                                  Start Date: 1/1/1998

                                 End date: 31/3/1998


Report output with default date axis

3 Default chart with 3Months Of Data.png

Dynamic axis chart output shows month wise data

4 Dynamic chart with 3Months Of Data.png

5) Date Range:    One Year

                                          Start Date: 1/1/1998

End date: 12/1/1998

Report output with normal date as the x-axis value

5. Default chart with 12Months Of Data.png

Report output with Dynamic Period as the x-axis value shows quarter wise data

6 Dynamic chart with 12 Months Of Data.png

6) Date Range:   Two Years

                                       Start Date: 1/1/1998

End date: 12/1/1999

Report output with normal date as the x-axis value

7. Default chart with 2 Years Of Data.png

Report output with Dynamic Period as the x-axis value gives year wise data

8. Dynamic chart with 2 Years Of Data.png

Note:

Reason for having Number and string for every formula is to avoid sorting issue of the Date field. This is because the output of the Dynamic Period is a String. Since the if statement contains combination of Date and String data types even the date is considered as String.

=If([NoofDays]<=31;[Invoice Date];If([NoofDays]<=90;[MonthYear(Mon-YYYY)];If([NoofDays]>90 And [NoofDays] <=365;[Quarter(YYYY-QQ)];[Year])))

The sorting issue in chart created only Dynamic Period which is of String Data type will be like below (if you see the order of dates as 11,12 & 7)

ASCII of 12 less than 7 so it came first)

Date Range Selected is : 7/1/1998 to 14/1/1998

Sorting Dynamic PEriod Chart.png

Chart Created using both Dynamic Period and Dynamic Period Number will be like this

Sorting Dynamic PEriod Number Chart.png

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply