Unable to use if and median in Line chart expression

[#1]

!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

[Anonymous]

Could you attach the app or data please

[#3]

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

Thanks in advance for the help!

[rwunderlich · Accepted solution]

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

[Anonymous]

Try this expression, it is working for me

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

[#6]

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.

[rwunderlich]

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

-Rob

[Anonymous]

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.

[#9]

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.

[Anonymous]

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.