topic Set Analysis and Charting question in QlikView

Set Analysis and Charting question

alexis — Thu, 05 Dec 2013 13:42:25 GMT

I have the following data structure where "Data" is linked to my Calendar via "CallDate" and also I hava e a data island called "SupplData" that has a field called "TerminalPeriod" which is of the same format as "Period" in Calendar which is a date field of the type YYYY-MM (e.g. 2013-12)

Below you can see 3 charts - the one on the left is a chart of the data in DATA where:

Dimension: Period

Expression: = count ({$}DISTINCT TID)

The one on the right is the data in the data island and defined as:

Dimension: TerminalPeriod

Expression: = sum({$<[TerminalPeriod] = {'$(eSelectedPeriod)'}>} TIPRecordNo)

where "eSelectedPeriod" is a variable that has the definition of Year & '-' & num(Month)

Year and Month are fields from the Calendar and are presented as Listboxes for the user to choose from

MY REQUIREMENT is to display the 2 measures together in the same chart. The bottom chart is just that and it uses the above expressions, i.e.:

Dimension: Period

Expressions: (for DATA) = count ({$}DISTINCT TID)

Expressions: (for SUPPL DATA) = sum({$<[TerminalPeriod] = {'$(eSelectedPeriod)'}>} TIPRecordNo)

When a specific month is chosen (as is the case above) everything works fine but I am more interested to see these pairs across a range of dates. Deselecting the month I get the following where the "combined chart" at the bottom does not show any figures for SUPPL DATA which we know exist as we can see thm on the chart below right.

Any ideas how I can achieve the correct pairing here... I tried things like:

= sum({$<[TerminalPeriod] = {[Period]}>} TIPRecordNo) etc .. and lots of other variations. The solution has to work for both a specific month (or months) as it works above and also for no selection in which case it will show the entire date range.

Thanks in advance

Alexis

Re: Set Analysis and Charting question

MK_QSL — Thu, 05 Dec 2013 13:50:13 GMT

If I am not wrong, you want to see your data/graph based on two different dates selection.

Please check below video on youtube...

Selecting Arbitrary Date Ranges...

Selecting Arbitrary Date Ranges - YouTube

Re: Set Analysis and Charting question

Gysbert_Wassenaar — Thu, 05 Dec 2013 14:12:02 GMT

Is there a reason why TerminalPeriod cannot be renamed to Period so your tables are associated properly? That way you don't have to deal with cartesian products as you have to now.

Re: Set Analysis and Charting question

alexis — Thu, 05 Dec 2013 17:55:49 GMT

The above example is just an extract from a more complex application. The main reason is that of granularity - it's a little like the classic Sales/Budgets scenario where the latter may only be available monthly and sales daily. For all the obvious reasons the two cannot be joined using the sales date in Calendar. Something similar is happening here. I am always open to other suggestions.

thanx for responding

Alexis

Re: Set Analysis and Charting question

Gysbert_Wassenaar — Thu, 05 Dec 2013 18:27:40 GMT

Not at all obvious if you're using Qlikview: Fact Table with Mixed Granularity

Re: Set Analysis and Charting question

alexis — Fri, 06 Dec 2013 07:55:42 GMT

Thanks Manish. This is an interesting video but it is not relevant to the problem that I described 😞

Re: Set Analysis and Charting question

alexis — Fri, 06 Dec 2013 08:03:59 GMT

Hi Gysbert

Thank you for the reply - The "Generic keys" approach is very interesting and I have used it elsewhere to very good effect, but I think in this instance might be overkill - all I want to achieve here is to map the 2 sets that have values of the same type (Period and Terminal Period) on the same chart and I am sure can be solved using Set Analysis.

Thank you for feedback

Alexis