Set Analysis between 2 variables

[hschultz]

Hi all

I am having some difficulty with a set analysis and i hope someone will be able to assist.

We want to have a column that works out Cases for the last 12 weeks.

I am using the below:

=Sum({<YEARWEEK_FULL={">=$(v12Weeks)<=$(vCurrentWeek)"}>}QV_Cases)

My variables are

vCurrentWeek = Today

v12Weeks = Today - 7 weeks

Now this all looks ok, but when i select a week on my model it all goes to 0, even if that week is in this range or not.

I cannot just add 1 in it, because we need it to change via the dimensions. Buit it cannot change according to dates.

When i add the following, it appears to work but my values are still out according when i make certain selections:

=Sum({<CAL_WEEK_NO=, CAL_MONTH=, CAL_QUARTER=, CAL_YEAR=, YEARWEEK_FULL={">=$(v12Weeks)<=$(vCurrentWeek)"}>}QV_Cases)

Is there maybe an easy way that i might be missing? I can unfortunately not post this as a sample.

Thanks in advance

[swuehl]

If your sum of cases is correct without making any selections, I would assume the field modifier for YEARWEEK_FULL is working correctly (first thing to check, do your variables match the required format).

If making a selection turns your result into incorrect ones, I assume that you are creating an invalid combinations of selections (i.e. your set expression selection in YEARWEEK_FULL is incompatible with your user selection).

If you modify a date field in your set expression, make sure that you disregard selections in any other date field a user may perform (like you started with CAL_WEEK_NO, CAL_MONTH etc.)

Are there any other calendar / date fields your user may select in?

[hschultz]

My variables work correctly as rerquired, but i think there might be an issue seeing as the final code incorporates AGGR functions. So i assume that the issue is with that function. Here is the code used for the final solution

sum(aggr(

  sum(aggr(

                                        sum({<CAL_WEEK_NO=, CAL_MONTH=, CAL_QUARTER=, CAL_YEAR=, YEARWEEK_FULL={">=$(v12Weeks)<=$(vCurrentWeek)"}>}QV_Cases),LONG_DESCRIPTION,BRANCH,L_PACKSIZE

                                        )

                              )

                    ,LONG_DESCRIPTION,BRANCH,L_PACKSIZE))

/

aggr(NODISTINCT

sum(AGGR(sum({<CAL_WEEK_NO=, CAL_MONTH=, CAL_QUARTER=, CAL_YEAR=, YEARWEEK_FULL={">=$(v12Weeks)<=$(vCurrentWeek)"}>}QV_Cases),BRANCH,DTS_REGION,LONG_DESCRIPTION,L_PACKSIZE)),DTS_REGION,BRANCH,L_PACKSIZE)

[sushil353]

Hi,

Why dont you try to get the vcurrentWeek from your dimension itself like

vCurrentWeek= Max(week)

v12Weeks = $(vCurrentWeek)-7

HTH

Sushil

[hschultz]

Hi and thanks for the suggestion.

The problem with that though is that if we are in week < 12, then the code will break, as for example:

vCurrentWeek= Max(week) = 10

v12Weeks = $(vCurrentWeek)-12 = 50

The user does not want this with accordance to selections, even if they select 2 years ago, the want the last 12 weeks from current date.

So we are building the variables like this:

vCurrentWeek     =(year(Today())*100)+week(Today())

v12Weeks          = IF(num(week(Today())) > 12,

                                (year(Today())*100)+num(week(Today())-11),

                                        (num(year(Today()))-1)*100+(num(week(today()))+41))

So my variables work correctly, to always give tghe last 12 weeks, and also stretch over a year change.

[swuehl]

Heinrich,

I don't really understand your setting, at first glance your multiple advanced aggregations seem quite complex and as Henric posted in one of his latest technical blog posts, you can get rid of the advanced aggregations in many cases, but to tell you possible alternatives, this would require that one can understand your data model and chart settings (so this means I don't have the necessary informations right now).

For example, a possible issue might arise from your use of multiple embedded aggregation functions (sum() ), but you applied set analysis to only part of them (which might be correct, might be incorrect depending on your setting and requirements).

In general, I would suggest that you don't use a periodic dimension like calendar week to select in an do calculations (i.e. calculate the week 12 weeks ago or similar) or use a calendar week with just year and week encoded in an integer (YYYYMM) )

This makes it quite hard to calculate a target week across year changes.

Instead, use underlying  QV date, like it is returned e.g. by QV function weekname. The string representation returnes a Year Week like YYYY / WW, but the underlying numeric value is a QV date. This means you can simply subtract 12*7 days to get the week  12 weeks ago.

Hope this helps,

Stefan