YOY data with pre-set date range filters

[akshaym]

Hi,

I am currently creating a YOY management dashboard, where the requirement is to allow the user to select the comparison period which is ‘1 Year’, ‘2 Years’, ‘3 Years’ and ‘5 Years’ from the parameter. Once the user chooses the period the sales reflect for that N periods. Which I was able to replicate through this post Filter with Pre-set Date Range - #3 by ArunSanthosh

The user further needs to use a date parameter where for a selected period they want to see the to-date sales. However, I am not able to achieve this requirement as whenever I select the date from the parameter, sales for the selected period reflect t- date sales, but the previous year sales show the entire sales for that year and not the sales till that date.

Here is my calculated field

ifelse(
    truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) <= {Created Date} AND {Created Date} <= addDateTime(-1, 'DD', ${DatePram}) AND ${DateRange} = '1 Year', 
    'Include',
    
    truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) <= {Created Date} AND {Created Date} <= addDateTime(-2, 'DD', ${DatePram}) AND ${DateRange} = '2 Years', 
    'Include',
    
    truncDate('YYYY', addDateTime(-3, 'YYYY', ${DatePram})) <= {Created Date} AND {Created Date} <= addDateTime(-3, 'DD', ${DatePram}) AND ${DateRange} = '3 Years', 
    'Include',
    
    truncDate('YYYY', addDateTime(-5, 'YYYY', ${DatePram})) <= {Created Date} AND {Created Date} <= addDateTime(-5, 'DD', ${DatePram}) AND ${DateRange} = '5 Years', 
    'Include',
    
    'Exclude'
)

The 2 parameters in the calculated field are:
1.) ${DatePram} = Date Parameter which the user selects from the control panel
2.) ${DateRange} = Is the parameter which contains comparison period ‘1 Year’, ‘2 Years’, ‘3 Years’ and ‘5 Years’

I would highly appreciate some tips/suggestions on how to achieve the desired outcome

Thanks

[Nico]

Hi @akshaym,

Please have a look at the following calculated field:

ifelse(
    truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) = truncDate('YYYY', {Created Date} AND 
	{Created Date} < truncDate('DD', ${DatePram}) 	AND 
	${DateRange} = '1 Year', 
    'Include',
    
    truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) = truncDate('YYYY', {Created Date} AND 
	{Created Date} < truncDate('DD', ${DatePram}) 	AND 
	${DateRange} = '2 Year', 
    'Include',
    
    truncDate('YYYY', addDateTime(-3, 'YYYY', ${DatePram})) = truncDate('YYYY', {Created Date} AND 
	{Created Date} < truncDate('DD', ${DatePram}) 	AND 
	${DateRange} = '3 Year', 
    'Include',
    
    truncDate('YYYY', addDateTime(-5, 'YYYY', ${DatePram})) = truncDate('YYYY', {Created Date} AND 
	{Created Date} < truncDate('DD', ${DatePram}) 	AND 
	${DateRange} = '5 Year', 
    'Include',
    
    'Exclude'
)

Best regards,
Nico

[akshaym]

Hi @Nico ,

Thank you for providing a solution, but this is not exactly my requirement. Apologies for that.

The requirement is to show the current period and the previous period. That is if I select the $DatePram to be 7/18/2024 and the $DateRange is ‘1 Year’ it should show me the total sales till date for 2024 (i.e. from 1st of Jan’24 to 18th of July’24) and sales up to 7/18/2023 (i.e. from 1st of Jan’23 to 18th of July’23) for 2023. Right now with the calculated field I shared initially it shows me the total sales till the end of 2023 (.i.e. from 1st of Jan’23 to 31st Dec’23).

In case of your calculated field it does the same thing shows sales for 2023 (.i.e. from 1st of Jan’23 to 31st Dec’23) and only for 2023, it excludes the current year altogether.

An additional requirement I had to the same use case was to pass an additional parameter which is a period selection - ‘Month’, ‘Quarter’, ‘Year’. Which when the user selects month and the $DatePram selected is 7/18/2024 and the $DateRange is ‘1 Year’ then the sales should reflect the MTD for the same period last year that is (1st of July’23 to 18th July’23). Similarly if $DateRange is ‘5 Years’ then total sales displayed should be for (1st of July’19 to 18th July’19), (1st of July’20 to 18th July’20), (1st of July’21 to 18th July’21), (1st of July’22 to 18th July’22), (1st of July’23 to 18th July’23), (1st of July’24 to 18th July’24).

And the same when a quarter or year is selected. Any tips/suggestions would be highly appreciated .

Thank you

[Nico]

Hi @akshaym ,

I see there is an error in my calculation. Lets try to solve the request with the 1 year part.

    truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) = truncDate('YYYY', {Created Date} AND 
	{Created Date} <= truncDate('DD', addDateTime(-1, 'YYYY', ${DatePram})) 	AND 
	${DateRange} = '1 Year', 
    'Include',

First line checks filters for data from the previous year (complete year).
Second line reduces the data to your required parameter minus one year.
Third line checks for the comparison.

Before we continue with your next request, let us solve the first one.

Best regards,
Nico

[akshaym]

Hi @Nico ,

Thank you for your continued support with regard to solving the use case I have been trying to figure out.

I however encountered issues with the calculation you provided. But the good news is that I was able to find solution for my 1st use case and below is the solution for that.

ifelse
(
  ${DateRange} = '1 Year' AND 
  (
    (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
    OR 
    (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})
    )
    ), 
  ifelse(
    truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram},
    'Include',
    'Include'
  ),
  ifelse(
    ${DateRange} = '2 Years' AND 
    (
      (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
      OR 
       (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) 
       OR 
      (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram}))
    ),
    ifelse(
      truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram},
      'Include',
      'Include'
    ),
    ifelse(
      ${DateRange} = '3 Years' AND 
      (
        (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
         (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram}))
      ),
      ifelse(
        truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram},
        'Include',
        'Include'
      ),
      ifelse(
        ${DateRange} = '5 Years' AND 
        (
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-4, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-4, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-5, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-5, 'YYYY', ${DatePram}))
        ),
        ifelse(
          truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram},
          'Include',
          'Include'
        ),
        'Exclude'
      )
    )
  )
)

I am still trying to figure out how to include another parameter which is a Period Selection which includes - ‘month’, ‘quarter’, ‘year’.

I want the user to basically choose one of the Period Selection choices and then it shows them MTD if choice selected is Month, QTD if choice is Quarter and so on. It should show them the values for the current chosen period selected from the {$DatePram} and then the previous period for that same time frame using the {$DateRange} parameter.

Any suggestions would be highly appreciated.

Thanks,
Akshay

[akshaym]

Hi,

In the previous calculated field I had some redundant logic, which I went ahead and updated. And like I mentioned, I want to add an additional parameter which is a ‘Period Selection’ parameter, which gives the user the ability to choose the period to be either a ‘Month (MTD)’, ‘Quarter (QTD)’, ‘Year (YTD)’. While I did add this in the calculated field as well. But it’s not working as excepted for the ‘Month’ and ‘Quarter’.

The issue arises when I select the period to be ‘Month’ (for example, if the {$DatePram} is set to 7/22/2024). In this case, for the MTD, I do not have any sales for MTD (July 2024), and if I have selected the {$DateRange} to be ‘1 Year’, and I do have sales for the previous MTD of ( July 2023 - 7/22/2023). What happens is it shows me only the MTD sales of 7/22/2023, which, let’s say, is 1. It shows this correctly, but it does not show anything for 7/22/2024. What I expected it to show was 2023 = 1 and 2024 = 0. The same happens with QTD, where I expect it to show as Q3 2024 = 0 and Q3 2023 = 15, but it only shows me Q3 2023 = 15

Could someone help me understand what I am missing and what I need to add to get the desired outcome.

ifelse
( 
/* This calculates the MTD for 1 Year */

  ${DateRange} = '1 Year' AND ${PeriodSelector} = 'Month' AND 
  (
    (truncDate('DD', {Created Date}) >= truncDate('MM', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
    OR 
    (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', $      {DatePram})
    )
    ), 
    'Include',

 /* This calculates the QTD for 1 Year */   
  
  ${DateRange} = '1 Year' AND ${PeriodSelector} = 'Quarter' AND 
  (
    (truncDate('DD', {Created Date}) >= truncDate('Q', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
    OR 
    (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', $      {DatePram})
    )
    ), 
    'Include',  

/* This calculates the YTD for 1 Year */    

     ${DateRange} = '1 Year' AND ${PeriodSelector} = 'Year' AND 
  (
    (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
    OR 
    (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', $      {DatePram})
    )
    ), 
    'Include', 

 /* This calculates the MTD for 2 Years */ 
  
  ${DateRange} = '2 Years' AND ${PeriodSelector} = 'Month' AND 
    (
      (truncDate('DD', {Created Date}) >= truncDate('MM', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
      OR 
       (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) 
       OR 
      (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})
      )
    ),
    'Include',

 /* This calculates the QTD for 2 Years */ 

     ${DateRange} = '2 Years' AND ${PeriodSelector} = 'Quarter' AND 
    (
      (truncDate('DD', {Created Date}) >= truncDate('Q', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
      OR 
       (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) 
       OR 
      (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})
      )
    ),
    'Include',

 /* This calculates the YTD for 2 Years */     

 ${DateRange} = '2 Years' AND ${PeriodSelector} = 'Year' AND 
    (
      (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) 
      OR 
       (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) 
       OR 
      (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})
      )
    ),
    'Include',

 /* This calculates the MTD for 3 Years */

      ${DateRange} = '3 Years' AND ${PeriodSelector} = 'Month' AND 
      (
        (truncDate('DD', {Created Date}) >= truncDate('MM', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
         (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})
        )
      ),
        'Include',

 /* This calculates the QTD for 3 Years */
      
      ${DateRange} = '3 Years' AND ${PeriodSelector} = 'Quarter' AND 
      (
        (truncDate('DD', {Created Date}) >= truncDate('Q', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
         (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})
        )
      ),
        'Include',     

 /* This calculates the YTD for 3 Years */
      
      ${DateRange} = '3 Years' AND ${PeriodSelector} = 'Year' AND 
      (
        (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
         (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})
        )
      ),
        'Include',  

 /* This calculates the MTD for 5 Years */                   
      
  ${DateRange} = '5 Years' AND ${PeriodSelector} = 'Month' AND 
        (
          (truncDate('DD', {Created Date}) >= truncDate('MM', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
          (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-4, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-4, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('MM', addDateTime(-5, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-5, 'YYYY', ${DatePram}))
        ),
          'Include',

 /* This calculates the QTD for 5 Years */  

  ${DateRange} = '5 Years' AND ${PeriodSelector} = 'Quarter' AND 
        (
          (truncDate('DD', {Created Date}) >= truncDate('Q', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
          (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-4, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-4, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('Q', addDateTime(-5, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-5, 'YYYY', ${DatePram}))
        ),
          'Include',

/* This calculates the YTD for 5 Years */    

  ${DateRange} = '5 Years' AND ${PeriodSelector} = 'Year' AND 
        (
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', ${DatePram}) AND truncDate('DD', {Created Date}) <= ${DatePram}) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-1, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-1, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-2, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-2, 'YYYY', ${DatePram})) OR 
        (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-3, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-3, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-4, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-4, 'YYYY', ${DatePram})) OR 
          (truncDate('DD', {Created Date}) >= truncDate('YYYY', addDateTime(-5, 'YYYY', ${DatePram})) AND truncDate('DD', {Created Date}) <= addDateTime(-5, 'YYYY', ${DatePram}))
        ),
          'Include',

          'Exclude'
      )

Thank you,
Akshay

[Nico]

Hi @akshaym ,

I am trying to understand where the issue is. I tried the first part (MDT for 1 Year) and it works on my sample data.

Easiest way to help you is, that you create a sample data set and upload it to QuickSight Arena including your calculation and the parameters. Then we can work togehter on it.

You can find more information here:

NEW - QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community

[FeatureTile] QuickSight Arena provides you with an embedded instance of QuickSight right within the QuickSight Community. Using Arena, you can: Publish dashboards to the Community Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order to learn / try answering questions Include dashboards, with working solutions, while responding to questions In this article, …

Best regards,
Nico

[akshaym]

Hi @Nico ,

I went ahead and published the dashboard in Arena

YOY data with pre-set date range filters

However, my requirement changed altogether. At the moment I feel YOY calculated fields I am able to get what the desired outcome is. However, the requirement now focuses on having a table where the user gets to see the # Of Licenses Created and # Of Licenses which Expired based on the ‘$TimeFrame’ a NEW parameter which is Q1, Q2, Q3, Q4, YTD. The $ComparisonPeriod stays the same.

If you see my dataset (image attached below for reference), I have two date columns. One is Created Date and the other one is Expiry Date. I want to just use a single date column and draw the table showing # Of Licenses Created and # Of Licenses Expired.

I was able to find a similar request in this post which shows how to merge two dates into 1 single date, but the output is not as expected in my use case. What would be the best method to get the desired result as shown below (the output I want - image attached below). (PS we currently do not use any SQL database where I can manipulate the dataset. The data directly comes from our proprietary software to the S3 bucket and then ingested in QuickSight. Hence whatever manipulation I need, I have to do it in QuickSight, if its doable)

Merging two date fields into a single date

@DannyV checking back in. Is this still an issue? Do all your other months work?

From the post the calculated fields I create. The # of Licenses Created matches only if the date column selected is Created Date and the # of Licenses Expired matches only if the date column selected is Expiry Date, otherwise the counts do not match.

Any thoughts on this and how I can achieve the desired outcome.

Thanks,
Akshay

[Nico]

Hi @akshaym
manipulating the data source would be helpfull. But as you mentioned, we search for a solution without data manipulation.

The analysis you shared looks good, when removing the filter.

Can you explain, what you want? Do you want to switch the time range parameter and compare the tickets on a quarterly level (Q1, Q2, Q3 or Q4 as you mentioned)?

Best regards,
Nico

[akshaym]

Hi @Nico ,

Yes that’s the user requirement. So it’s going to be in a table format, with # Of Licenses Created, # Of Licenses Expired, with their % difference. The controls allow the user to see it for all 4 quarters and the YTD.

[Nico]

Hi @akshaym ,

Did you test the “merging” function? Is the function and data valid?

Best Regards,
Nico

[akshaym]

Hi @Nico,

Applying the merge function, when I show the counts separately, like # Of Licenses Created using creation date it shows the correct count. Similarly, when I plot the # Of Licenses Expired using the expiry date it shows the correct count. However, when I plot the two and use the creation date for group by the count of # of Licenses expired shows a wrong count and similarly, when I use the expiry date in the group by field the # Of Licenses Created count messes up. I am unable to show the correct count using only 1 date in the group by field well.

I am also tagging in @abacon, since they provided that solution to the post I tagged in my previous reply. And if they know anything that I am missing that needs to be added to fetch the correct count.

[akshaym]

Following up on the above request. I was somehow able to find a close by solution, but yet its not correct. I would appreciate if anyone could help me out to understand what am I missing or how can I tweak the logic to get the desired output.

I first created an ifelse statement which is as follows
Named as - check1

ifelse({Expiry Date} >= ${YEAR}, {Created Date}, {Expiry Date})

Where ${YEAR} is a rolling relative date of yesterday

I created two additional fields

1.) CreatedCount

distinct_count(ifelse({Expiry Date} >= ${YEAR}, {Company Name}, NULL), [check1])

2.)

distinct_count(ifelse({Expiry Date} <= ${YEAR}, {Company Name}, NULL), [check1])

Based on this logic, what happens is that it shows me a count of licenses which are active. However, this makes the wrong assumption that only those many licenses were created for that year. For example, say in the year 2017 I sold 4 licenses, out of which 1 expired in 2018, 1 in 2023, and 2 in 2024 (out of which 1 expired in July, and 1 is yet to expire in November).
This logic shows you the #Of Licenses Sold in 2017 as 1, where ideally it should show 4.

Any suggestions or help would be highly appreciated.

Thanks,
Akshay

[Nico]

Hi @akshaym ,

your questions are not that easy

Is this your desired outcome?

You can find the analysis here:

Tickets Expiry Date and Create Date

The basic function to get the combinded year field looks like this:

ifelse(
truncDate(“YYYY”, {Expiry Date}) = truncDate(“YYYY”,${YEAR}),
truncDate(“YYYY”, {Expiry Date}),
ifelse(
truncDate(“YYYY”, {Created Date}) = truncDate(“YYYY”,${YEAR}),
truncDate(“YYYY”, {Created Date}),
ifelse(
truncDate(“YYYY”, addDateTime(1,“YYYY”, {Expiry Date})) = truncDate(“YYYY”,${YEAR}),
truncDate(“YYYY”, {Expiry Date}),
ifelse(
truncDate(“YYYY”, addDateTime(1,“YYYY”, {Created Date})) = truncDate(“YYYY”,${YEAR}),
truncDate(“YYYY”, {Created Date}),
NULL
)
)

)

)

Best regards,
Nico

[Brett]

Hi @akshaym,
It’s been awhile since we last heard from you. Did Nico’s suggestion(s) above help accomplish your solution or did you have any additional questions?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[akshaym]

Hi,

Apologies I forgot to provide an update on this.

Thanks to @Nico for helping me out with this requirement, but the solution was not helpful and I understand my requirement was not straightforward to achieve with the way my data was structured. And I appreciate @Nico’s help with coming up with a solution.

I was able to use this post and build my requirement. Count columns based on creation date and resolved date in the same table - #4 by Jesse

Thanks,
Akshay

[Nico]

Hi @akshaym ,

somehow I thought it is not possible for you to do SQL statements in your datasource. Guess I mixed it up.

If you solved your issue, you can mark your post as solved.

Best regards,
Nico