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
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])
Thank you for help. I am on vacation, but when I come back to office I will try 8)
Quote from: blom0344 on 13 Jun 2013 03:05:58 PM
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
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