topic Re: Comparing Values with Different Calendars in QlikView

Comparing Values with Different Calendars

Mon, 18 Mar 2013 14:23:25 GMT

I have two fact tables, each with a separate date, and also joined by another dimension. I've got a date island built, and I use that for my filters without issue.

Where my problem lies is when I need to use the measures in the same chart. For example, I need to divide the measure from one table (a count) by the measure in the other (hours). I can't seem to figure out how to get the measure to work in a chart with a date that it doesn't belong to. I've tried:

sum(if(Count_Calendar.MonthYearNum = Hours_Calendar.MonthYearNum, Count))

sum({<Count_Calendar.MonthYearNum={Hours_Calendar.MonthYearNum}>}Count)

...and others. Anyway, how do I get a chart to work correctly in this case?

Re: Comparing Values with Different Calendars

hic — Mon, 18 Mar 2013 14:34:21 GMT

If it is a measure, I assume that it is some kind of aggregation, e.g. Sum() or Count(). Then you can just divide one aggregation with the other:

Sum(Amount)/Sum(Hours)

Sum(Amount)/Count(distinct OrderID)

I would avoid if() inside the Sum() as far as possible. It's slow. Further, the Set analysis is just a way to make the chart make a calculation based on an other selection than the current. So that won't help you either.

HIC

Re: Comparing Values with Different Calendars

Mon, 18 Mar 2013 14:49:21 GMT

When I do that, and I put a single date on the axis, what happens is that one of the measures is always for all time (since its calendar isn't on the chart). Amount and Hours have two separate calendars. Giving them the same calendar ends up making the data wrong, due to there being Amounts when there aren't Hours, or vice versa.

Re: Comparing Values with Different Calendars

hic — Mon, 18 Mar 2013 14:52:59 GMT

I need to see the data to understand...

HIC

Re: Comparing Values with Different Calendars

Mon, 18 Mar 2013 17:23:03 GMT

Sorry for not being clear. I've solved my own problem by creating a link table to connect the two by combining keys with the date key.