Month control selection

Question & Answer
, , , , , , , ,
1

Hi,
I’m currently developing a QuickSight dashboard where I need to calculate the number of tickets created and closed each month. I’ve implemented a month control based on the creation date. In this scenario, if a ticket was created in January but closed in February, both the creation and closure counts are going to January, but I need count like created in Jan 1 and closed in Feb is 1 Below, I’ve provided the input data and the expected output. Can you please assist here?

test

2

Hi @hellosai321 ,

is it possible to manipulate the data? The following structure makes a solution much easier:

image

Best regards,
Nico

1 Like
3

Hi @Nico ,

Is there any workaround for this in quicksight analysis itself without manipulating the structure of the data?

Thanks,
Sai

4

Hi Sai,

you can create one table for aggregating the created tickets and one for the closed tickets per month. Is that an option?

Regards,
Nico

5

Hi @Nico ,

I dont have much exposure to dataset preparation as other team will work on it. I’m checking to see if there is any workaround for this in quicksight analysis itself.

Thanks,
Sai

6

Hi @hellosai321 ,

My suggestion is to build to visualisations in your analysis, one for created tickets and one for the closed ones.

image

Regards,
Nico

7

Hi @Nico ,

is there a way to have single month and single year control if I want to show created and closed in separate visuals?

8

Hi @hellosai321 ,

do you want a filter option for a single year and a filter option for a single month that works on both separate visuals?

BR
Nico

9

Hi @Nico ,

Yes, I would need a single filter for month and year.

10

Hi @hellosai321

here I created one filter that extracts the month and year of your date. The result is a single field that you can filter on.

concat(
    toString(extract("MM", {Order Date})),
    "-",
    toString(extract("YYYY", {Order Date}))
)

You can also have a look on the analysis I created in QuickSight Arena:
Single month and year filter

Best regards,
Nico

11

Hi @Nico ,

I’m looking for single month and year control based on created and closed dates as ticket may created in January but closed in May also. Above calculation is straight forward and it gives the count of created in January and if it closed in May also falls in January. Is there a way based on our previous communication?

Thanks,
Sai

12

Hi @hellosai321
I created an example with 3 lines of your example data.
Ticket Analysis Filter

Is this filter what you need?

Best Regards,
Nico

13

Hi @Nico ,

Could we consolidate the filters for both visuals into one, rather than having two separate filters? My requirement is to display both created and closed instances in a single bar chart, based on one month on the X-axis.

Thanks,
Sai

14

Hi @hellosai321 ,

You could solve this with a parameter, but with one filter only tickets are counted that are created AND closed in the same month.

You can find it here.
Ticket Analysis One Filter

Please go to Sheet 2 (1) and select a date in January.

Best Regards,
Nico

15

Hi @Nico ,

Have you created parameters for the ‘created month’ or ‘closed month’ in the analysis? In Sheet 2, for January, the ‘created count’ should be 4, and the ‘closed count’ should be 1 according to my sample data. Similarly, for February, the ‘created count’ should be 1, and the ‘closed count’ should be 2.Could you provide the solution as per this sample data?

16

Hi @hellosai321

I added the missing lines to the example. For the next time, please login to QuickSight Arena, add your data and create an analysis. Then helping you is easier.

You can find the solution here:
Ticket Analysis One Filter

To count your created tickets the calculation looks like:

//Created Tickets
ifelse(
    (
        truncDate("YYYY",${DateFilterParameter}) = truncDate("YYYY",created) 
        AND 
        truncDate("MM",${DateFilterParameter}) = truncDate("MM",created)
    )
    , 1
    , 0
)

To filter your tickets the field looks like:

//FilterField

ifelse(
    (
        truncDate("YYYY",${DateFilterParameter}) = truncDate("YYYY",created) 
        AND 
        truncDate("MM",${DateFilterParameter}) = truncDate("MM",created)
    )
    OR 
    ( 
        truncDate("YYYY",${DateFilterParameter}) = truncDate("YYYY",closed) 
        AND 
        truncDate("MM",${DateFilterParameter}) = truncDate("MM",closed)
    )
    , "Y"
    , "N"
)

Best regards,
Nico

17

Hi @Nico ,

I’m also looking for the similar scenario for my dashboard.

The solution is not clear and is confusing. On which field have you created a parameter {DateFilterParameter}? Can you please provide a step-by-step solution as I tried but was unable to do it. Is there a way I can see your calculations in Arena?

Thanks,
Sri

18

Hi @Srinath
welcome to the community.

Thank you for your feedback!

The Parameter is not based on a specific field. It represents a date, that the user can choose.
The link provided in my answers should redirect you to arena. There you can copy the analysis. Then you can edit and view every calculation.

I will add a new answer, that explains the steps in more details.

Best regards,
Nico

19

Thanks @Nico for the response.

I will look into it.

Is there a way to show number of created, closed and backlog(open) in a bar chart together instead showing them in a separate visual as my customer wants to see them together?

Thanks,
Srinath

20

Hi @Nico ,

Can we have range in control like Start Date and end date so that I can select range from Jan to April to know the number of created and closed between the range. Even the created and closed counts zero it should show zero for those months .