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

[#2]

How does your dimension YEARWEEK_FULL looks like. Is week 14 in 2013 displayed as 201314?

In that case i think you must have a variable v12Weeks that calculates: (Year(Today()) & Week(Today())-12)

[jolivares]

Public a QVW of what exactly you want...

[hschultz]

The variables are working correctly i have tested within a Text object.

[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