LAC-A function count issue

[Dom]

Hi,

I’m trying to implement a calculated field with LAC-A function as per documentation:

When i try to implement it i get this error:

I noticed someone else had the issue but the thread was archived in September.

Error in the group by count function

@shirakoresh - Did you check if the ‘month’ field is designated as Dimension in QuickSight? If the ‘month’ field contains numeric values, sometimes QuickSight automatically assign it as Measure due to its numeric metadata. However you can use the “Convert to Dimension” feature to ensure that it is designated as Dimension in QuickSight, which is a pre-requisite for that LAC function to work like the way you are looking for. Hope this helps! [image] Did my suggestion help you in resolving your q…

Does anyone know of a solution to this issue?

[robert.eibers]

Hey @Dom ,
I assume t_customer_reference is a calculated field? If it gives you two values try to change it or maybe you could show us this field,

BR
Robert

[Dom]

Hi Robert,

t_customer_reference is not a calculated field. It has just all the trade codes and some are repeated for the different counterparty . I need to get as a result how many times a code is repeated . Also counterparty is not a calculated field.

Both field contain strings value.

eg . t_customer_reference = “23473646388389” and Counterparty = “bob”, "mark etc.

Thanks,

Dom

[robert.eibers]

Then you should raise a ticket to AWS Support - Creating support cases and case management - AWS Support (amazon.com)
But out of curiosity, does the same happen when you choose a different group-by level or try to count something else with or without a group-by level? I can’t reproduce the error and would like to know which part is causing it.

[Dom]

Hi Robert,

After trying a few things I realized that the LAC custom field formula work if i create the field in the analysis. I previously was trying to create the field on the dataset where i usually prefer to create my calculated fields.

Unfortunately I don’t think this formula is useful for what I’m trying to achieve.
My end result should be returning a Boolean that identifies which trade codes are unique and which trade codes are not unique so i can then filter my visualizations to contain only unique trades or not unique trade codes.

Any suggestions on how i could achieve this?

Thank you
Best,

Dom

[robert.eibers]

Hey @Dom ,

sorry for the late reply, but something like:
ifelse(count(your field) < 2, what you want as a result, NULL)
could work. That should be the right result, first asking if the count is less than two - so unique and if it is you get the desired outcome for this fields.

Hopefully you can work wit this.

BR
Robert

[Dom]

Hi @robert.eibers ,

Thank you for getting back to me.
I have tried that formula but I cant use the result as a filter. It just returns an aggregate if I put it a table and if I use it in a filter I get an error when retrieving the values.

I think is just counting the number of total values in the reference column is not evaluating the single value of the row and check if is repeated.

Best,

Domenico

[robert.eibers]

Ok, i tried it with some dummy data:
ifelse(count({Contact Name}, [{Contact Name}])<2,‘Unique’,‘Not Unique’)
Maybe this can help, it makes the filter also easy - it should be a custom filter with the values Unique and Not Unique.
It gave every contact name the value unique, which is unique and all the other not unique and counted them.

[Dom]

Hi Robert,

Thank you very much i will give it a go and let you know.

Best,
Domenico