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
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)
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
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)
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
Hi Techies
Any updates/suggestions on my requests Please
Regards
CogPlan
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
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
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
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
As me and blom mentioned earlier, this can be done in ONE query , no joins or unions. See attached