I used to have a graph showing the age of my tickets :
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.
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.
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 ?
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)
@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:
@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
@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))
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.
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 ?