MTD YTD in Crosstab-Critical

[cogplan]

Hi Techies
      Currently I have a report to be generated in crosstab.
      Data is available based on dates and in the date dimension, we have dateid, date, month, and year.
      I have to create CY YTD, CY MTD, PY YTD, PY MTD based on user selection of month, year and product group
   
     I have created 4 queries , Main query consisting of all the measures and passing the parameters from other 3 prompt queries.

      CY YTD =  IF(([YEAR_CODE] =?Year?) AND ([MONTH_NUM_OF_YEAR] between 1 AND ?Month?))
THEN (running-total([SALES FACT].[VALUE(Qty)]))
Else (0)

     CY MTD= [Sales Fact].[Value(qty)]

    But I am not getting the correct value for YTD , as its just adding the values .
    Anyone please guide me in this for the query.
.http://www.cognoise.com/Smileys/default/sad.gif

     Its a very critical issue, and have to complete the request asap..


Thanks & Regards
CogPlan

[cognostechie]

Try removing the running-total from the expression.

IF
    (
      ([YEAR_CODE] =?Year?) AND ([MONTH_NUM_OF_YEAR] between 1 AND ?Month?)
    )
THEN ([SALES FACT].[VALUE(Qty)])
Else (0)

[cogplan]

Hi CognosTechie
          I tried removing running-total from the query, then it gives me only value for a particular month and doesn't provide me value for YTD.
      I am new to this and have to complete before weekend... :( :( :-[

CogPlan

[cognostechie]

I don't know what you are doing, what type of Prompt you have and what is the value you are putting there but this always works for me. This doesn't even require a Prompt:

For CY YTD, I always use this:

If
(
extract(year,[Date Dim].[Calendar Date]    ) = extract(year, current_date) and
[Date Dim].[Calendar Date]   <= _add_days(current_date,0)
)
then ([Qty])
else (0)

Try this:

IF
    (
      ([YEAR_CODE] =?Year?) AND ([MONTH_NUM_OF_YEAR] <= ?Month?)
    )
THEN ([SALES FACT].[VALUE(Qty)])
Else (0)

[cogplan]

Hi CognosTechie
              Thanks a lot for your response. CY MTD, LY MTD , CY YTD LY YTD .. all the calculations are working fine.
Currently I am having issues joining those queries.
  all my queries have these columns
   1>Department/Location
  2> Prod Grp Name
    3>SKU Name
    4> Measures= Revenue ( Query 1- CY MTD, Query 2 LY MTD)
     I have tried many join conditions, but nothing seems to be working right for the report.
     Have made two queries for MTD join and two more for YTD join. Hence looking for some guidance from the fella members.
      Kindly do let me know if any ideas/suggestions for joins. Its critical reqt and I have to finish this over the weekend.

Regards
CogPlan
   

[cogplan]

Hi Techies
          Any updates/suggestions on my requests Please
Regards
CogPlan

[blom0344]

I received  your PM , but am at a loss when you mention 'joining queries' If you use if then else or CASE constructs you should be able to get the correct 'buckets' of data within 1 query.

As cognostechie showed this should be covered by 'normal' totalization, certainly not by running totals

[cogplan]

Hi Blom
           As per my requirement, I would require CY MTD, LY MTD, CY YTD, LY YTD , Something like this

   Division    Prd Grp Name   Prd Name     CY MTD   LY MTD   CY YTD   LY YTD
     1                    ABC                  XYZ        100      99               210    220
         Currently I have created CY MTD and LY MTD joined into MTD
         Similar way CY YTD and LY YTD joined into YTD
         MTD and YTD joined into Main query
         
         Values for individual queries is perfectly fine. But unable to get desired output for joined queries. Please find attached xml of the report. Please do reply me back when you are free

Regards
CogPlan

[RKMI]

Hi Cogplan,

Ok, there were a few things I would do differently. Like the way you were making those outer joins didn't have any true MTD based on three data items with 0..1 relation and YTD is based on Sales ID with 0..n, I think that a big reason why your report had errors. Anyways I went with approach of using unions and use placeholders.

+1 applaud, please.  ;D

Thanks,
RK

[cogplan]

Hi RKMI
           Thanks for making changes to the report and also my thinking. Report specifications are changing day by day.
           Yes, I was wrong in somewhere with the joins , as I am new to reporting.
           Once again thanks for taking time and resolving my issue.

Regards
CogPlan

[cognostechie]

As me and blom mentioned earlier, this can be done in ONE query , no joins or unions. See attached