Problems with Relational model

[b737]

Hi, I am currently dealing with one Relational model, so I have a few questions, and I will try to show all in one example, which is closely to this one: City and Code_Are are grouped, and year, crosstab model, where dimension is number of sold products [Sold]

                                   Year
City  Code_Area
        Code_Area

             or like this
           
                                    2006   2007  2011  2013
                             
         Code_Area1        2
City1 Code_Area5
         Code_Area7                   1

         Code_Area1        2
City2 Code_Area5
         Code_Area7                    1
.................
          Code_Area1      4
Total Code_Area5
         Code_Area7                     2

Questions:

1. Since model is Relational, [Year] I can see just in one dimension, how to filter [Year] in way to see this distinct years, only 2006,2007, 2011 and 2013, because I can only use operator between or to choose just one year, and for this it does not work.

2. How to create query calculation which will for example subtract years like this [year2007] - [year2006], in order to obtain the difference in sold products in 2007 and 2006

3. How to create Total which will sum Code_Area1 for all City in report, and it is also Relational model, you can see what I mean in the example.

Thank you for help

[blom0344]

1. [year] =  2006  or [year] = 2007  or [year] =2011 or [year] = 2013   (filter expression)


2. total(case when [year]=2007 then [sold] else 0 end) - total(case when [year]=2006 then [sold] else 0 end)

[may have to experiment with this one..]

3. Something like  :  total([sold] for [Code_area])

[b737]

Thank you for help. I am on vacation, but when I come back to office I will try  8)

[b737]

2. total(case when [year]=2007 then [sold] else 0 end) - total(case when [year]=2006 then [sold] else 0 end)

Hi, it's me again. This works great in the GO Sales model, but in my relational model gives me a negative difference of millions although numbers are three-digit. Do you know what could be the problem and how to fix it.

thank you

[blom0344]

Alas , I do not know either your model or your data. I was just giving an example with - in small print - a hint to change the expression to suit your needs