topic Re: Unable to use if and median in Line chart expression in QlikView

Unable to use if and median in Line chart expression

Wed, 04 Feb 2015 18:06:42 GMT

!Hi,

I am new to Qlik view.This may seem like an amateur question.

I am building a line chart with year on the X-Axis which can be drilled down to month and median(JobEndTime-JobStartTime) on Y axis. I want the runtime for only one source system and so I am doing this

=if(source_system='XYZ',median(JobEndTime-JobStartTime),NULL())

The chart is blank and says No data to display. It looks correct but I at a loss as to why it isnt calculating the median.

Any help would be appreciated.

Thanks,

Sreekar

Re: Unable to use if and median in Line chart expression

Anonymous — Wed, 04 Feb 2015 18:08:32 GMT

Could you attach the app or data please

Re: Unable to use if and median in Line chart expression

Wed, 04 Feb 2015 18:12:26 GMT

I've attached the app. The issue is in the 2nd tab named FDM.

Thanks in advance for the help!

Re: Unable to use if and median in Line chart expression

rwunderlich — Wed, 04 Feb 2015 18:25:25 GMT

The if() belongs inside the median(). But it's better to do it with Set Analysis like this:

Avg({<Current_Load={FDM}>}endTime_EST-startTime_EST)

That's the expression from the qvw you uploaded.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Unable to use if and median in Line chart expression

Anonymous — Wed, 04 Feb 2015 18:38:04 GMT

Try this expression, it is working for me

=avg({<Source_System={FDM}>}(endTime_EST-startTime_EST)). I have done it in your app.

Re: Unable to use if and median in Line chart expression

Thu, 05 Feb 2015 19:16:31 GMT

Quick question - If I were to just subtract the two columns, then also, If is not working. If I want to do this,

if(Current_Load='FDM', endTime_EST-startTime_EST)

This is also not working and I am not sure how I can use set analysis here. Its really confusing.

Re: Unable to use if and median in Line chart expression

rwunderlich — Thu, 05 Feb 2015 19:29:50 GMT

sum({<Current_Load={FDM}>}endTime_EST-startTime_EST)

-Rob

Re: Unable to use if and median in Line chart expression

Anonymous — Thu, 05 Feb 2015 19:42:52 GMT

If you calculate on the dimension of year then for source system FDM and for a given year 2015 you have multiple values of start time and multiple values of end time so either you will have to sum or average those multiple values. If you had just a single value of start time and end time for 2015 then you could subtract and show on the graph. For better understanding I have created a table box and list box on sheet 2. Please review. There is no issue with if function.

Re: Unable to use if and median in Line chart expression

Thu, 05 Feb 2015 19:43:18 GMT

Thank you but I was looking at something without an aggregation function.Just to get the difference so that I can drill down to a daily basis.

Re: Unable to use if and median in Line chart expression

Anonymous — Thu, 05 Feb 2015 19:53:33 GMT

For any year when you have multiple end and start date, there will be multiple subtractions. Then either you will have to sum or average those subtracted values to show as a datapoint for a particular year..2014 or 2015.

Re: Unable to use if and median in Line chart expression

rwunderlich — Thu, 05 Feb 2015 20:35:42 GMT

The aggregation function is just a device to be able to use a Set expression. If there is only one qualifying row, It should give the same result.

-Rob