CountOver with dates

Question & Answer
, ,
1

Hi, I have a question about countOver function. Say I have the following table:

image

c1/c2 are dates and *_co are count over values. They are counting the id with MM/YYYY values of their respective date columns.

What I want to do is to have a column where I count c1 with c2s MM/YYYY partition. If this works, this new column should hold following values:

Expected –
0 // No Aug-2024 in c1
0 // No Jul-2024 in c1
1 // There is 1 Jun-2024 in c1
1 // There is 1 Jun-2024 in c1
1 // There is 1 Jun-2024 in c1
2 // There are 2 May-2024 in c1
0 // No Apr-2024 in c1
1 // There is 1 Mar-2024 in c1

I really hope this is doable. Ideas with countOver or any other approach is much appriciated!

Thanks in advance!

2

Hi @skulltima

Welcome to the community!

To achieve the desired result in Amazon QuickSight, where you want to count occurrences of dates from column c1 based on the month/year of dates, you can use a combination of calculated fields and the countOver function.

Example : To get MMM YYYY from date

concat(toString(
                ifelse(extract('MM', Date) = 1,'Jan',
                    ifelse(extract('MM', Date) = 2,'Feb',
                        ifelse(extract('MM', Date) = 3, 'Mar',
                            ifelse(extract('MM', Date) = 4, 'Apr',
                                ifelse(extract('MM', Date) = 5, 'May',
                                    ifelse(extract('MM', Date) = 6, 'Jun',
                                        ifelse(extract('MM', Date) = 7, 'Jul',
                                             ifelse(extract('MM', Date) = 8, 'Aug',
                                                ifelse(extract('MM', Date) = 9, 'Sep',
                                                    ifelse(extract('MM', Date) = 10, 'Oct',
                                                        ifelse(extract('MM', Date) = 11, 'Nov',
                                                            ifelse(extract('MM', Date) = 12, 'Dec',
                                                            ''
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        ),' ' ,toString(extract('YYYY', Date)))

image
image835×666 25 KB

Example : (Syntax may vary)

countOver({c1_MMYYYY},[{id}],PRE_AGG)
3

hi, thanks a lot for the reply!! i tried this, but unfortunately it didn’t work.

I wasn’t sure about the {Date} field you are referring to, so I tried with both c1 and c2 values in 2 different tries. If I use c1 to create a new calculated field named c1_MMYYYY - it simply creates a value series similar to c1 without the day component (using c2 yields a similar result.

Now, doing countOver for this column as suggested (countOver({c1_MMYYYY},[{id}],PRE_AGG)) creates a column with 1 as the value. Not the expected result. What I want is what is outlined in Expected column from original post - counting c1 values partitioned by c2 month-year values.

4

Hi @skulltima

The Date field is used from my data its just for reference, and i see you already corrected with your data c1 and c2

To understand more about your requirement to help, could you please create a sample analysis in Arena and explain further.

1 Like
5

Hi @skulltima,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial topic or were you able to find a work around?

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

Thank you!

6

Hi @skulltima,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!