Set Analysis Documentation

[#1]

Dear All, I'm a newly joined buddy.

I've been using qlikview for many years but on custom modeled solutions ( since V 5.* ) that didn't allow me to dive into new, exciting Qlikview features, such as Set Analysis.

Perhaps, I'm wrong but it seems to me that standard users manual doesn't provide an "academic" guide to the syntax, just a few examples on its usage.

Can anyone provide me a good Pdf? I also work with OLAP on Microsoft and some "set analysis strange behaviors" leave me incredulous.

As an example, I've downloaded your .qvw app that explains and provide examples of usage of set analysis for YTD calculation.

On that app, i've tried to get a pivot with just 3 columns : "Date" ( dimension) , "Current sales" and "Previous Year Sales" ( expression )

While "current sales" ( that is Sum(LineSalesAmount) ) works fine ( obviously ! ) , the "Previous Year Sales" doesn't .

It's defined as " =Sum({$ < Date ={$(=(AddMonths(Date,-12)))}>} LineSalesAmount) "

It might be a good example for Trend Analysis ( current year vs previous year ).

Can anyone help either with a pdf or answering this? Thanks, it's greatly appreciated!

Fabrizio.

[#2]

I believe the Set Analysis section in the Reference Manual is the only official documentation available. This forum is also a great resource. Utilizing the search feature may find other posts looking for similar answers.

Perhaps the number one tip when using Set Analysis and dollar sign expansions is to create a chart using your expression, but don't give it a label. Then when the chart is rendered, the label will be your Set Analysis expression with the dollar sign expansion evaluated. That should tell you if the dollar sign expansion is correct. Sometimes dates can be tricky due to formatting issues. You need to figure out what the data in your date field looks like and make sure your dollar sign expansion is evaluating to something in the same format.

In your previous year expression, nothing looks too out of the ordinary. One quick guess would be that you need quotes around the dollar sign expansion:

=Sum({$ < Date ={"$(=(AddMonths(Date,-12)))"}>} LineSalesAmount)

EDIT: Sorry, I didn't look close enough. It looks like you are referencing the dimension date in your Set Modifier. That does not work. The dollar sign expansion is more of a universal expression, it is not reevaluated for each record. In one of my applications, we've loaded up Previous Year values as separate fields in the data set, so for each date, we have a Current Year Sales and Previous Year Sales value for each date.

[#3]

Hi NMiller ( hope name it's right ! ). Yes, it's as you say, it doesn't simply work, dimension is evaluated upon the whole "dataset" . It's a big pity because it doesn't avoid the development ( and the loading too ) of different measures per each row.

So if I wish to use set analysis for showing ( as an example ) the %absorption of sales of a representative on the total sales of HIS area ( and not the whole country ) it's hard using set analysis, isn't it? I simply face the same problem, I can't compare sales against my direct ancestor ( i'm using olap terminology but it should make sense, i hope )

It's not a problem, I mean, i already did it in version 5.5 ( as sum ( TOTAL <AREAMANAGER> representativesales ) ) but i hoped it was possible with this sophisticated feature !!

Thanks again for your truly kind answer, Fabrizio