How to use Aggregated calculated field as dimension?

Question & Answer
,
1

Hello,

I used to have a graph showing the age of my tickets :
image

The dataset changed and now there is no longer only one row for each ticket so I had to aggregate almost all my calculated fields by ticket ID using “avg”. The field “Stock_Age” is used as X in the previous picture, the field “Ticket_Age” is used by Stock_Age.

“Stock_Age” =

ifelse(isNull({Ticket_Age}),"Solved",
    {Ticket_Age}<=1,"Today",
    {Ticket_Age}<=2, "Yesterday",
    {Ticket_Age}<=7, "This week",
    {Ticket_Age}<=31, "This month", 
    {Ticket_Age}<=91, ">1month", 
    {Ticket_Age}<=181, ">3months",
    {Ticket_Age}<=365, ">6months",
    {Ticket_Age}<=540, ">12months",
   ">18months")

“Ticket_Age” =

avg(
    ifelse({New filter}="N", NULL, dateDiff({Creation_Date},${pAnalysis_Date}))
,[{ID_ticket}])

Since I added “avg” in Ticket_Age, my graph has this error :
“Level aware calculation (LAC) aggregate expressions as dimension is not supported. It is supposed to be used as a parameter of a visual aggregate function”

I saw on this forum that last september it was not possible to use LAC as dimension as well, but is it still not possible today ? Do I have a way to avoid this ? I tried using avgOver but it doesnt work for my usecase.

Thanks

2

When you say avgOver doesn’t work for your use case, do you mean that it doesn’t give you the correct result or are you getting an error message?

avgOver(
ifelse({New filter}=“N”, NULL, dateDiff({Creation_Date},${pAnalysis_Date}))
,[{ID_ticket}], PRE_AGG)

1 Like
3

Hello David,

When I use avgOver for my calculated field when its used in a graph as a dimension it works fine, but it doesnt give me the expected result when I use it as “value” in a table. Is it normal ? Am I supposed to use 2 different calculated field : avgOver when I need it as a dimension, and Avg when I need it as value in a table ?

4

Also I used to sort this dimension by a 3rd calculated field, so its sorted by time and not by A-Z ascending :

“Stock_Age_Sort” =

ifelse({Stock_Age}="Today",1,
{Stock_Age}="Yesterday",2,
{Stock_Age}="This week",3,
{Stock_Age}="This month",4,
{Stock_Age}=">1monts",5,
{Stock_Age}=">3monts",6,
{Stock_Age}=">6monts",7,
{Stock_Age}=">12monts",8,
{Stock_Age}=">18monts",9,
10)

It doesnt work anymore when I apply “sort by” :
“A field used in this visual is no longer available in the current dataset. You can change which fields are available by editing or replacing the current dataset”

I don’t really know why it says a field doesnt exist because this calculated fields refers only to “Stock_Age” which exists because it’s my X axis …

5

When you used avgOver, did you specify the calculation level (PRE_AGG or PRE_FILTER)? If you don’t specify any calculation level, it means the aggregation is performed based on your partition and the dimensions present in your visual. If your bar chart and your table visuals don’t contain the same dimensions, then it’s expected that they don’t show the same results.

My guess is that you used:
avgOver(
ifelse({New filter}=“N”, NULL, dateDiff({Creation_Date},${pAnalysis_Date}))
,[{ID_ticket}])

You should add PRE_AGG at the end like this:
avgOver(
ifelse({New filter}=“N”, NULL, dateDiff({Creation_Date},${pAnalysis_Date}))
,[{ID_ticket}], PRE_AGG)

1 Like