0

I have a fact table with the following structure:

PeriodID, AccountID, FlagID, Value1

PeriodID, AccountID and FlagID all link to different dimensions Value1 is a numeric value, with a Sum() measure specified on it in the cube

Assume the following data:

PeriodID, AccountID, FlagID, Value1
20140101, 123      , 1     , 100
20140201, 123      , 2     , 200
20140301, 123      , 1     , 300

My requirement is to build an Excel CubeValue formula which returns Value1 for the last Period in the quarter (i.e. 20140301), if FlagID had a value of 2 at any point in that quarter.

I managed to retrieve Value1 for each AccountID, for the last month of the quarter. What is left is to filter out records whose FlagID was never set to 2 during the quarter.

How would you go about this? What changes to the cube would need to be in place to support this logic in excel? Can one somehow link a fact to itself just like you would like a fact to a dimension?

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
user1365247
  • 337
  • 7
  • 17
  • What do you want to display if FlagID did not have a value of 2 in the whole quarter? Try if the FlagID had a value of 2 in any previous quarter and then use that? Show null? – FrankPl Apr 08 '14 at 19:13
  • if FlagID was never 2 in the quarter, then that records for that AccountID should be left out completely... – user1365247 Apr 09 '14 at 06:53
  • 1.) Thus, the requirement is to generate this formula for a certain AccountId and all PeriodIDs where thers is no FlagID with 2? 2.) Can we assume that there are some dimensions Period, Account, and Flag, and for simplicity assume their only relevant hierarchy is the key attribute hierarchy named PeriodID, AccountID, and FlagID? – FrankPl Apr 09 '14 at 16:20
  • Guys check out [this proposal for a dedicated Business Intelligence Q&A site right under Stackechange umbrella. Throw all your sample BI questions there.](http://area51.stackexchange.com/proposals/70503/business-intelligence?referrer=EPHSm8-3avvaMxLjdRIeNg2) – bonCodigo Jun 01 '14 at 07:07

0 Answers0