Issues working With Dimensional Data

[sag4cognos]

Hello All,

I have been trying to solve a problem for some time and I guess, its time I need a fresh pair of eyes to look at the problem. I am using Cognos 8.4 BI with TM1 9.5 cubes

My Date Dimension is like this.
Sep YTD
  |-> Beg Bal
  |-> Oct
  |-> Nov
  |-> Dec ( goes on till Sep)

This is repeated for all months. ( Sep YTD, Oct YTD, Nov YTD.. etc)

In the report, I have allowed the end users to pick the Current Month and also the Current YTD.
I have individual queries having slicer filters for the time period. i.e, one query has slicer filter for Current Month and the other has slicer filter for CYTD.

Now the issue comes up when I am filtering.  If the specific customer has sales( computed across dimensions Products, Region ) for the Current YTD, then he should figure in the Current Month query.

To put it in simpler terms, I need to compute Customer Sales( computed across Products, Region ) for the CYTD within the Current Month Query.

I tried using calculations such as Total(CurrentMeasure) Within Detail(CurrentYear)
With the Current Year = Ancestor(CurrentMonth)

But I am not able to compute the sales. I am able to get the sum of the default measure, but when I need to compute it for other dimensions, the query does not work.

Can any one suggest me any ideas on this? ???

Thanks a ton for your help,
Sagar

[rockytopmark]

Well, from your explanation, I can't visualize exactly what you are trying to do... can you post an Excel mockup or something describing your required output?
(you can add attachments from within the Additional Options link below the text entry box)

[sag4cognos]

Hello RockyTopMark,

Thank you for your time.

I have uploaded a document with the following screen prints.
http://www.scribd.com/Cognos-Issue/d/24597107
1. Shows you the time dimension structure.
2. Shows you the current Month Query with the slicer filter highlighted.
3. Shows you the Current Year to Date Query with the Slicer filter highlighted.
4. Shows you the output.
5 and 6 - The changes I made to the query.

Issue :  I am suppressing rows with zeros in both queries. What that makes is if a customer does not have sales for the current month but has sales for the current year, he/she will be filtered from the Current month query, but will figure in the Current Year to Date Query. I do not want to eliminate that customer.

What I did :
1. Tried to build CYTD sales in the current Month query. But I got the sum only for the default measure and not for the other measures.
2. I did manage to bring in both CYTD and CM members into a single query. But it works when I hard code the members. I am passing both the dimensions ( CM and CYTD ) as prompts and the cross tab fails to run.

I understand, I am missing a point here. Please advice. Please do let me know if you need further information.

Thanks,
Sagar