Reference line on y-axis

[ioannagr]

Hi all. I need your help once again!

I've got a line chart, with a dimension of Date (master calendar field) and

a measure of sum(<{Availability={'Available'}> [count]) to get the daily sum of count for each date when the item is available.

I wanted to do a reference line on y-axis and i did it like that :

avg (total aggr(sum({<[Availability]={'Available'}>}[count]),[Date]))

but it gives me the wrong answer. The most count encountered is about 60.000 and this expression i did gives me 665.000 for avg.

Please help

[lironbaram]

hi 

in the referance line you don't need the total as it calculated disregarding the dimension 

might it be you have values that are assigned to null value at the date field 

[ioannagr]

That is true, i do have null values for the date

How do you explain the latter?

first column is the date, second is  the aggr (sum) i mentioned, third columns is the sum i mentioned.

I don't understand the very last line... How come for null dates the sum is this huge number but the aggrsum is null??!

How do i fix this?

[lironbaram]

hi 

a quick fix will be like this :

avg (total aggr(sum({<[Availability]={'Available'}>}if(len(Date)>2,[count])),[Date]))

but i would have try to understand why you have null values in the date and fix it in the load process