topic Re: Assign values based on select criteria in QlikView

Assign values based on select criteria

anuradhaa — Tue, 03 Mar 2015 16:46:31 GMT

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.

Re: Assign values based on select criteria

JonnyPoole — Tue, 03 Mar 2015 16:56:54 GMT

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.

Re: Assign values based on select criteria

anuradhaa — Tue, 03 Mar 2015 17:18:39 GMT

pls see attached. what i need is yellow one

Re: Assign values based on select criteria

JonnyPoole — Tue, 03 Mar 2015 17:33:12 GMT

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 ?

Re: Assign values based on select criteria

Wed, 04 Mar 2015 09:50:34 GMT

please have a look at the QVW attached:

Changes in Dimension and Expression