Brainstorm: Valueloop/Valuelist How to...?

[#1]

A question for thinking.

As most of us know - we can use in our Set Analisys Dollar-Expressions to define some complex values - for example - dates.
For example:

sum({$<Date={">=$(=date(monthsstart(1,vDate,-1))) <$(=date(monthsstart(1,vDate,0)))"}>} Cost)

This example will give us over our dimmension total cost of something within the month where the date vDate is in:

If vDate = 23.11.2010 then expression is sum({$<Date={">=01.10.2010 <01.11.2010"}>} Cost)

But what if we have more coplicated task - we must show some count of peirods in history and enumerate them in table - Expression for this looks like this:

sum({$<Date={">=$(=date(monthsstart(1,vDate,-i))) <$(=date(monthsstart(1,vDate,1-i)))"}>} Cost)

where "i" is an iterator that can be 1 to n.

The easiest way of solving this problem as I dreame ( 🙂 ) was using of ValueLoop... thow there is a problem - ValueLoop can't transport it's value over the brackets of the Dollar-Sign Expression.

If something like

sum({$<Date={">=$(=date(monthsstart(1,vDate,-valueloop(1,3,1)))) <$(=date(monthsstart(1,vDate,1-valueloop(1,3,1))))"}>} Cost)

is possible - then one question - How?

May be let's make a Brainstorm?

PS.

Somewhy I still can't attach files - example of all this text is located here: http://rapidshare.com/files/438376472/8.qvw

[johnw]

So in the example file, you want a dimension that is generated by ValueLoop, and you want to refer to this dimension's value in the Set Analysis, hoping to generate something like your "Three periods of retroview" chart without needing three separate expressions?

The problem goes much deeper than set analysis not working with valueloop(). Set analysis doesn't work with ANY dimension value, calculated or not. A set is only generated once for the entire table, not once per value of the dimensions. It doesn't matter if it's a real dimension or a calculated dimension, you can't refer to its values in the set analysis.

Being able to generate a set per combination of dimension values would be very useful, but I suspect we will either never see this feature, or only see it in the far off future.

However, this problem can be solved without set analysis at all by using an AsOf table that connects AsOfDates to previous months. See attached. I put the AsOfDate selection and the resulting pivot table beneath your chart. The dimensions are Dim and MonthsBack, and the expression is a simple sum(Cost). All the intelligence is in the data model, not in the chart.

[#3]

Hi,

I do have similar question

Let's say, I create a calculated dimension with ValueList(v_dateselection,v_previousmonth)
v_dateselection contains: Feb 2011
v_previousmonth contains: Jan 2011

Now, on the expression tab, I would create such expression:

if(ValueList(v_dateselection,v_previousmonth)=v_dateselection,count({$<OpenMonth = {'$(v_dateselection)'}>}SD),count({$<OpenMonth = {'$(v_previousmonth)'}>}SD))

I understood from the QV Help, that I need to "restate the valuelist function with the same parameters in the chart expression"
But I feel that it would be simpler (but I can't make it work) with such expression (as my variable OpenMonth is have the same format as my 2 variables):

count({$<OpenMonth = {"= $(OpenMonth) = $(=ValueList(v_dateselection,v_previousmonth))"}>}SD)

Have you experience such scenario?
Thanks in advance,