topic Comparing dates with set analysis in QlikView

Comparing dates with set analysis

Anonymous — Fri, 05 Jun 2009 14:41:30 GMT

My problem is the different time dimensions which have to "work together" in the chart. There is also a third time dimension, which isn't related into the data structure.That could also be a variable.

Example:

Sum (if (A_MonthID = MonthID, A_Sum))
/
Sum(if (B_MonthID = MonthID, B_Sum))

This example works just fine with normal If-clause. The solution is - however - very heavy when there are 10 millions of rows and some other if-conditions in the both clauses. (And in the future the chart should actually show bars on every day on the selected month.)

Because of that I would like to use the SET-analysis. I don't know if it's possible. It would work easily if you have just one time dimension but this case is different.

Any ideas or code-examples?

-Marcus

Comparing dates with set analysis

Fri, 05 Jun 2009 15:03:23 GMT

Hi Markus,

please see the attached file if this solves your problem.

Rainer

Comparing dates with set analysis

Anonymous — Fri, 05 Jun 2009 16:06:05 GMT

Ok. This solution works fine in this case. The table structure in the real case I'm working with is much more complicated and it's not possible use the date field as a Key field.

Thanks anyway .

Comparing dates with set analysis

Fri, 05 Jun 2009 16:34:52 GMT

Markus,

see the next example. It will hopfully show a direction to develop your script.

Rainer

Comparing dates with set analysis

Anonymous — Fri, 05 Jun 2009 16:48:15 GMT

Hi Rainer

This structure looks very good and might give me some answers. I've bumped into many problems with time dimensions and I really need better solutions in the future. This seems to work excellent.

Thanks.

-Markus

Comparing dates with set analysis

Fri, 05 Jun 2009 16:49:56 GMT

Hi Markus,

You might also want to take a look at Oleg's wiki article about using flags; the example he mentions (some kind of 'same month last year' calculation) looks a lot like your case.

The URL: http://community.qlik.com/wikis/qlikview-wiki/using-flags-to-avoid-if-formulas.aspx

With regards,

Martijn