Set analysis depending of a selected field in a tree view list box

[#1]

Hi,

on a my report I have created a tree view list box to show year and month dimensions. For it, I have created a specific year_month field by joining year and month. In this way, an user can select year = 2010 + month = 9 and year = 2011 + month = 3 and so on.

Then I have created two pivot chart, the first one with some measures with yeartomonth data for each month, and the second one with monthly measures. In other terms, in the firts pivot chart if I show measures related to year = 2011 and month = 3 it is as I show the measure values between january and march 2001, while measures in the second pivot are really monthly amounts. My source db is made in this manner.

So, when I make one or more selections on the tree view list box in the second pivot chart I must show the measures respect to the selected year and with month minus or equals to the selected month. I need to find the right set analysis expression. It must be similar to this:

sum({$<Month = {"<=$(varSelectedMonth)"}>} MeasureAmount)

but I add the part related to the year.

This expression must function when I select year = 2010 + month = 10 and year = 2011 + month = 3. For these selections, I must show 2010-01, 2010-02, 2010-03, 2010-04, 2010-05, 2010-06, 2010-07, 2010-08, 2010-09, 2010-10 and 2011-01, 2011-02, 2011-03.

Moreover, I have the problem to extract years and months selected from the tree view list box and put the right values on a variable to pass to the set analysis expression. I can select year = 2010 + month = 8 and year = 2011 + month = 3 and year = 2011 + month = 5 and so on; in other terms, I can made a multiple year + month selection.

Any suggests to me, please? Thanks

[#2]

Hi,

can you attach an example?

Andrea

[#3]

Unfortunately no. The sample should be a project.

I have tried to explain it.

Thanks

[#4]

I have tried successfully to create the STATIC expression that I want to implement as a dynamic one:

sum({$<Year = {2010}, Month = {"<=12"}> + $<Year = {2011}, Month = {"<=3"}>} MeasureAmount)

This expression matchs with the multiple selection Year = 2010 + Month = 12 and Year = 2011 + Month = 3. But I can select more year and month couples in my tree view list box.

Thanks

[#5]

Any suggests to solve this issue? How can I render this set analysis expression for + symbol?

Is it possible to create an expression inside SUM() and before the MeasureAmount to create the right set analysis expression?

Thanks

[ToniKautto]

It would be really beneficial if you could attach a sample QVW, to clarify the data model and your current objects.

If your app conatins sensitive data, please use the Scrambling function to make data unreadable. Also feel free to remove sheets and objects that are not relevant to this specific scenario.

Settings > Document Settings > Scrambling

[#7]

Ok, but I've the QV in Italian language. How the Scrambling function is translated?

However, the problem is about how calculating a sum by union of sets. The sets are known only at run-time. So I can talk to dynamic union of sets inside a set analysis expression.

Thanks

[#8]

Ok. In Italian the Scrambling function is named as "Maschera".

I have attached my sample file. In the first sheet I have the tree view list box with caption "9. Anno - Mese - ...".

In the sheet 2 I have the pivot named "Personnel Effort". I'm trying to render dynamic the expression named "test". For my purposes, "test" expression functions well. In test2 expression I'm trying to solve this issue.

Thanks

[ToniKautto]

The first thing you need to do is to resolve all the synthetic keys that you currently have in the document, as you can see in the the table viewer (press Ctrl + T in desktop client). By having these synthetic keys you are very likely to end up with incorrct results in your calculations.

Synthetic keys appear when your tables use multiple fields as keys, instead of unique key fields. You can also end up in problems when performing aggregation on key fields, so it can be wise to create unique key fields that you never use in calculation but only for linking tabels together.

Before evaluating calculation/presentation problems further, please work on eliminating the synthetic keys from your data model.

[#10]

Hi Toni,

I'm agree with you that synthetic keys could cause some performance problems but for me it is important to separate CodiceCommessa (in English, CommitteeCode), Anno (in English, Year), Mese (Month), etc. There aren't any circular references.

Have you read the entire post, please? The issue, synthetic keys or not, is to create a set analysis expression with a dynamic union of sets. Is it possible to do it? If not, solving the synthetic keys is useless for me. I cannot lose too time.

Many thanks!