topic Re: Conditional Chart display in QlikView

Conditional Chart display

Mon, 03 Sep 2012 03:16:14 GMT

Hello,

I currently query a view which pulls out the following columns.

ClientName (String), TradeDate (DateTime) and Value (Decimal)

I would like to display only the ClientName in a listbox (I do not want to display TradeDate) Then I need to show a chart (Line Chart) which will display the TradeDate on the x-axis and the sum of Value on the y-axis (Expression). I also have two calendar controls which will be used by the user to select a range of date. The dates selected will be stored in two variables named MinDate and MaxDate. Now what I need is

1. Show the chart (line chart) only when the user selects a single clientName from the listbox. To display the chart without the clientName selected makes no sense.

2. Filter values in the chart (Before display) based on the TradeDate. i.e. show values in the line chart which satisfies the condition (TradeDate >= MinDate and TradeDate <= MaxDate). For eg. our query to the view might have lot of values with different tradedates from say 1st jan 2009 to Current date. But when the user selects a date range from the calendar control (1st Jul 2012 - 10th July 2012) then we need to filter our values based on this to be displayed in the line chart.

Any help will be greatly appreciated.

Cheers

Ranjith

Re: Conditional Chart display

Jason_Michaelides — Mon, 03 Sep 2012 06:46:20 GMT

Hi Ranjith,

1. On the layout tab of the line chart, in the conditional show box enter GetSelectedCount(ClientName)=1

2. Rather than calendar controls, which will need a trigger to effect the TradeDate once selected (and triggers should be avoided if not really necessary) maybe think about using a slider which is directly linked to TradeDate.

Hope this helps,

Jason

Re: Conditional Chart display

Mon, 03 Sep 2012 06:57:54 GMT

Hi Jason,

#1 - Worked like a wonder.

#2 - Users do not need a slider. They want a calendar control. I have already implemented this calendar controls and have two variables bound to them. Now, I need to filter the values in my line chart based on the vStartDate and vEndDate variables. Any help on this?

Cheers

Ranjith

Re: Conditional Chart display

whiteline — Mon, 03 Sep 2012 07:29:58 GMT

1) I suggest you consider calculation condition on general tab instead. And then edit the message (Error messages button) for calculation condition unfulfilled.So that the user understand why your chart dissapears.

Or you could mark ClientName as Always One Selected.

2) Provide an expression that you use.

Or try to modify it using set analiysis (see help).

Re: Conditional Chart display

Mon, 03 Sep 2012 07:50:44 GMT

Thanks for #1.

#2 - Let me explain this in a bit more detail. I have two sheets in my QlikView application. One sheet queries data from view1 which returns lot of columns and one column is a TradeDate column. I have also created two calendar controls in sheet 1 so that the user can select a date range to display the values. For this date range to work I created two variables as below and have bound them to the two calendar controls.

vStartDate - Bound to calendar control 1

Min Value - =Min({1} TradeDate)

Max value - =Max({1}TradeDate)

Format Pattern - DD MMM YY

vEndDate - Bound to TradeDate column from view1

Min value - Same as above

Max value - Same as above

Format Pattern - DD MMM YY

Next I have created a trigger on these two variables (Select on Field).

vStartDate -> Field - TradeDate, Search String - ='>='&vStartDate&'<='&vEndDate

vEndDate -> Same as above

In sheet 2 I query data from another view called view2. I get the columns ClientName, TradeDate and value. I display the client name in a listbox and then create a line chart with TradeDate as dimension and Sum(value) as expression. Here is the catch I now want to display values in this chart only for the trade dates selected on sheet 1 which is available in the two variables.

Can this be done? If so, how?

Cheers

Re: Conditional Chart display

Jason_Michaelides — Mon, 03 Sep 2012 07:52:48 GMT

If you insist on the calendar objects (which are ugly as well as fiddly in my opinion) then you have 2 choices:

1. Use a trigger or a button to set the selected values of TradeDate to be between the variables. Be aware that triggers are not always fired however - for example on use of Back and Forward buttons and Bookmark selections.

2. As whiteline suggests, use some set anaylsis in your chart expression to only look at dates between your variable values. The potential issue with this approach is it doesn't actually make the TradeDate selection which you may require to effect other charts etc.

Post your app and we can help further.