Show Current Month vs Last Month comparisons for the date range selected in a Combo Bar - Line Chart

[ytqv]

Hi All,

I have a date field called "Created Date" for which I am giving users an option to select a date range using 2 variables - vStart Date and vEnd Date. For the date range selected by the users using these variables, I want to show the total count of IDs by Month-Year on a bar chart in which the bar should show the data for current Month and the line should show it for the same time last month.

Sample data set below:

ID    Created Date

1       3/20/2018

2       3/18/2018

3       3/5/2018

4       3/9/2019

5       3/20/2019

6       3/21/2019

7       3/30/2019

If user selects, Mar 1, 2019 to Mar 30, 2019 using the variables, then the bar chart should look like:

x axis - (Month- Year format )-- Mar 2019

Y axis  - Bar- bar should show - count(distinct ID for current year month)      -- the result in this case should be 4.

Y axis - Line- Line should show - count(distinct ID of last year same month, ie, March 2018 )--- the result should be 3 in this case.

I can create just a month field using the created date and show 2 years of comparison, but the requirement is to have x axis show Month-Year (Created Date) for the range that user has selected using variables and then have a line on top of the bar to show data for same month Last Year. 

I've tried many things, but it doesn't work. Any help would be greatly appreciated. Thank you!

[Gysbert_Wassenaar · Accepted solution]

The problem you're experiencing is that the user selects dates in 2019 and the chart should show data from 2018 on an axis that only has Month-Year values from 2019. You simply can't show data from one dimension value at another dimension value. So you need to change the data model. One way is to join data from 2018 to data from 2019 so you get a table that looks something  like this;

Created Date, ID, ID_LY
3/20/2018, 1
3/18/2018, 2
3/5/2018, 3
3/9/2019, 4
3/20/2019, 5, 1
3/21/2019, 6
3/30,2019, 7
3/18/2019,  ,2
3/5/2019,  ,3

Another alternative is an As-Of table where you create a new dimension that links the dates from the different years.

AsOfDate, Create Date, Period
3/1/2018, 3/1/2018, CY
3/2/2018, 3/2/2018, CY
...etc
3/31/2018, 3/31/2018, CY
3/1/2019, 3/1/2018, LY
3/1/2019, 3/1/2019, CY
...etc
3/31/2019, 3/31/2018, LY
3/31/2019, 3/31/2019, CY

Then you can use the AsOfDate field as dimension in your chart and as the field in which your variables apply the selection. The expressions you can use in your chart are:

• 2018: count({<Period={'LY'}>}Distinct ID)
• 2019: count({<Period={'CY'}>}Distinct ID)

[ytqv]

Hi there! thanks a lot. I used the As-Of table concept and it works! Thank you!