different columns of my report needs a different filter condition

[unarayan]

I'm using report studio to generate a report that has teh following columns:

'product' 'YTD sales' 'QTD sales' 'Last years sales'
--------- ----------- ----------- ------------------

columns 2,3 and 4 query the sales fact table over a different time frame (YTD-year to date, QTD quater to date). How do I do this?

I new to Cognos, so please give me detailed instructions.

Thank you for any help. I'll greatly appreciate it.

 

[DoubleD]

YTD Sales = SUM(IF(YEAR(SALES_DATE)=YEAR(NOW())) THEN (SALES_AMT) ELSE (0))
YTD Sales = SUM(IF(YEAR(SALES_DATE)||QUARTER(SALES_DATE)=YEAR(NOW())||QUARTER(NOW()) THEN (SALES_AMT) ELSE (0))
Last Year Sales = SUM(IF(YEAR(SALES_DATE)=YEAR(NOW())-1) THEN (SALES_AMT) ELSE (0))

|| being a concatenation. I'm still used to Impromptu, but these calcs should give you the idea.

I am what I am based on the decisions I have made.

DoubleD

 

[unarayan]

Awsome approch....thank you. I'll try that.

 

[roberda]

unarayan,

did it worked for you as I have the same issue but could not make it work the way DoubleD proposed it in RS,

thanks for your answer

david

 

[umag]

I would assume that you have date prompt for this report.

Then you filter out the data till last year based on the prompt date and follow logic given by Double D. One query with embedded logic at column level.

or else you can have list queries for each data set i.e ytd, qtd and last year based on your date prompt and show them side by side in presentation layer.

Report performance varies for each approach.

Regards
Uma Mahesh

 

[unarayan]

roberda, I still have not had any sucess with DoubleD's suggestion....but I got sidetracked and have not tried hard enough.

Umag I liked your suggestion of creating multiple list queries for each data and show them side by side. Can you give me details on how to go about this.
How do you create multiple list queries in the same report?
will you need to join each of the different list queries to make sure all the queries display the information for the same product?
Will teh display format of the report be the same as the one shawn above?

Thank you for all your suggestions and help. It is greatly appreciated.