Help with custom measure


#1

I am looking to setup a custom measure for a histogram where the dimension is date and the custom measure has the following code:

select consumption from mytable where location='home' 

Its not working. consumption is a numerial field (float). table has only 3 columns : datestamp, location, consumption.


#2

you cant directly from immerse and you should use a view, but what you want to achieve with that query? if you want to sum/avg or whatever only records that has location equals to home you can do with a case expression


#3

So with that query, I am looking to filter the underlying data for the histogram to be only for location=home. So not looking to apply a global location filter to the entire dashboard but just for that histogram chart.

How do I create a view?


#4

if you want to sum only consumption for attribute location = ‘home’ you have to create a custom measure with a case statement

e.g. sum(case when location = ‘home’ then consumption else 0 end), but you are not exactly applying a local filter to the object but just zeroing the consumption 's value when the location != ‘home’.

you can create view from immerse sql editor or mapdql command


#6

The case statement worked for me. all set. thx.