Assign values based on select criteria

[anuradhaa]

Hi,

i have a data set like below,

InDate	OutDate
1/16/2015 0:00	31-01-2015
1/20/2015 0:00
2/3/2015 0:00	20-02-2015
1/10/2015 0:00	29-01-2015

here i calculate avg(outdate-inDate) and display in bar chart. i use month is dimension.

the problem i have is OutDate null value. So i need to assign values for this null based on select month. in month select box i use month(enddate)

If user select 2015 jan all null values should contain '2015-01-31'(we can use max(select month) here., if February '2015-02-28' and if no selections (max(outdate))

please help me to solve this.

[JonnyPoole]

For the first part something like this ?

avg(   if(  isnull(OutDate), max( total month(enddate)) , OutDate) - InDate )

i didn't fully understand the '2015-02-28' scenario though ... you can do an additional check for whether a user made a selection using  getselectedcount() ... but i would suggest replacing your list box expression with a data model field that you calculate in the load using month(enddate), then you can refernce the new data model field directly in the getselectedcount() function.

[anuradhaa]

pls see attached. what i need is yellow one

[JonnyPoole]

I can open your document but it didn't clarify the need to me:

It is this sentence that i cannot interpret with confidence:

"If user select 2015 jan all null values should contain '2015-01-31'(we can use max(select month) here., if February '2015-02-28' and if no selections (max(outdate))

please help me to solve this."

Let me try to paraphrase:

If a user selects '2015-jan' as a month from the list box, the null value should be replaced with the max date from the selected month(s). --> i think i understand

If a user has made NO selections for the month, then the null value should be replaced with the max outdate from all the outdate values in the table.  -> is that right ? 

[#5]

please have a look at the QVW attached:

Changes in Dimension and Expression