Derive Data - Cannot use measure as dimension

Visuals, Dashboards and Apps
1

Hi,
I have tried to use derived data (save expression as derived data subquery). In this definition, it has both dimensions and measures. Then I want to use measures from derived data as dimension, the validation went OK but it cannot produce chart or create SQL query.
For example:
|Date|Name| Test1| Test2| Test3|
|—|---|—|---|
|1/1|A|50|60|40|
|1/1|B|30|40|30|
|1/2|A|100|100|100
|1/2|B|50|50|50

Data needs to average all the dates first, become
Name| AvgTest1|AvgTest2|AvgTest3
A|75|80|70
B|40|45|40

Then Categorised how many people are failed using case e.g
Dimension: Case when AvgTest1 > 50 and AvgTest2 > 90 Then ‘Pass’ Else ‘Fail’ END
Measure: Count(distinct(Name))

Above example just to show in fact we have other fields like date time so Name can be duplicated, data needs to average over perod.

I have tried to use derived query from save expression but still cannot make it work.
Please advise.
Thanks.
error_agg

2

@Stima_Livingpresent what visual type are you using? I don’t think it should cause any problems.

This training video walks through a derived data setup: 09. Advanced - Derived Data

And on a recent post we perform top/bottom K chart: Visual showing dynamically showing Top / Bottom K

As follow up to the top/bottom K example, I am able to use the derived data subquery measure in a calculated dimension using CASE statement.

And the dimension from derived data subquery and apply aggregate.

image
image.png2396×1154 276 KB

1 Like
3

Hi,
I have followed the video by using save expression as dimension. I can use case when for measure. It can show in the table. Then another column count(name), I cannot put this either dimension or measure. it gives error Visual 21496 - Could not load data
(None, 0) Expected state FINISHED, but found ERROR
The intention is to use Pie chart but now testing on table.
Capture Capture2
Any advise is welcome.
Thank you very much.

4

I guess there is a bug in my environment or I misunderstand.
In Derived data definition:
Object name is defined in definition as Mytest. (In fact My test is Expression Name (Label).
Then when using name as [Mytest] so it gives error but if I use [name], I can use in both dimension and measure.
As later if we do not remember which alias has been defined we need to refer to Derived definition, and this could lead to wrong objects/error.

Please refer to the capture below.

1 2

Thank you very much.

5

@Stima_Livingpresent the label “MyTest” is what you want to use, which is the alias of the dimension in the derived data.

  1. What version of arcadia are you using?

  2. What connection is your dataset in?

6

After trying several times, yes I can use alias as dimension. We just had arcadia upgraged to 4.5 yesterday. Works nicely.
Thanks

1 Like