How to use Aggregated calculated field as dimension?

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

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

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 ?

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 …

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)

2 Likes

@David_Wong I have similar issue and I already use PRE_AGG but still not able to change rank as a dimension, do you know what is the issue for my code:

latest transact_date_by_Wk/Mo:
maxOver(({transaction_date}),[{reporting_year_adj},{Wk/ Month}],PRE_AGG)

Ending UAC $:
ifelse({transaction_date} = {latest transact_date_by_Wk/Mo}, unapplied, 0)

Rank function:
denseRank([max(sumOver({Ending UAC $},[carriergroupname],PRE_AGG)) DESC])

@yingkz What error are you getting?

@David_Wong The calculation does not have issue. But I am trying to change the Rank function above to dimension not measure, because I am trying to put it as the first row of each column in pivot table visual table and sort carrier group by rank function. Currently even though I select ascending sort carrier group by this rank function, it doesnt work

@yingkz
The reason you can’t use the rank as dimension is because you have a measure (max) inside the denseRank function.

Can you explain what you’re calculating with this max function?

max(sumOver({Ending UAC $}, [carriergroupname], PRE_AGG))

Depending on what you’re trying to calculate, you may be able to rewrite this as a dimension.

@David_Wong I need to show past N weeks $ amount by Carrier Group, I am not able to create the denserank calculation if I did not add max.
I also tried this by it stills not working: denseRank([maxOver(sumOver({Ending UAC $},[carriergroupname],PRE_AGG)) DESC]),[{Wk/ Month}],PRE_AGG))

@yingkz

I also tried this by it stills not working: denseRank([maxOver(sumOver({Ending UAC $},[carriergroupname],PRE_AGG)) DESC]),[{Wk/ Month}],PRE_AGG))

There’s a syntax error in your calculated field.

Forget about the maxOver for now and try this:

denseRank([sumOver({Ending UAC $},[carriergroupname],PRE_AGG) DESC], [{Wk/ Month}],PRE_AGG)

Let’s break your problem into small pieces. If you want to use your calculated field as dimension, focus on that first. If you can use the above calculated field as dimension but it’s not giving you correct values, you can look at other changes you can make to give you the correct result.

Sorry, there was a typo. It should be denseRank instead of densRank.

Thank you it works! But seems like the rank number is incorrect, same carrier group name can display in different rank number

Can you share a few rows of sample data so that I can better understand why the rank that you’re getting is incorrect?

Hi @David_Wong , I have the similar issue with average and custom aggregated field to dimension as well. For Example ,
Average Weight={Weight Lost} /count({Weight Difference})
weight difference = {before weight}-{after weight}
weight lost = sumIf({Weight Difference},{Weight Difference}>0)
Here I want to segregate the bar colors based on the “Average Weight” for that I have to use this in dimension, which is showing error (custom aggregated field is not allowed as a dimension).
How can I achieve my goal ?