Year ago Calculation along with Current Year Calculation

[arun_prs]

Hi Guys, I am really stuck at revenue calculations for Year ago. When the user select a range of calender dates for instance Mar 1 2009 to Mar 31 2010. He wants to see revenue for that time period and also Previous years calculations ie. Mar1 2008 to Mar 31 2009.

I tried _add_years(?calender_date?,-1) with if and case statements but I am not getting Year ago calculations, but I get 0.  Do you have any thoughts about this ?

[juliaf]

Hey,
I am not sure if this is the best way, but how we do it in our environment is as follows:
- Do not filter the query by the date range (this is important!) - the filters should be embedded in the calculation:
- You will need to define 2 metrics:
1. Selected Date Revenue:
IF (date between ?startdate? and ?enddate?)
THEN (revenue)
ELSE (0)
2. Selected Date Prev Year Revenue:
IF (date between _add_years(?startdate?,-1) and _add_years(?enddate?,-1))
THEN (revenue)
ELSE (0)
**assuming you have two prompts for start and end date
You can then do year over year growth...etc.

[arun_prs]

I Tried this by just putting in the calculation rather than in filters for prompts but I am getting 0 even for current select


<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-us">
            <modelPath>/content/folder[@name='AN Business Intelligence']/folder[@name='IT - Information Technology']/folder[@name='Cognos Samples']/folder[@name='Packages']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItem name="Current Year Select Revenue"><expression>IF (CAST([Sales (query)].[Time dimension].[Date],DATE) BETWEEN ?START DATE? AND ?END DATE?) THEN ([Sales (query)].[Sales fact].[Revenue])
ELSE (0)
</expression></dataItem></selection>
               </query>
            </queries>
            <layouts>
               <layout>
                  <reportPages>
                     <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
                        <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
                           <contents>
                              <list refQuery="Query1" horizontalPagination="true" name="List1">
                                 
                                 
                                 
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="ls"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              </list>
                           </contents>
                        </pageBody>
                        <pageHeader>
                           <contents>
                              <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
                                 <contents>
                                    <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
                                       <dataSource>
                                          <staticValue/>
                                       </dataSource>
                                    </textItem>
                                 </contents>
                              </block>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="ph"/>
                              </defaultStyles>
                              <CSS value="padding-bottom:10px"/>
                           </style>
                        </pageHeader>
                        <pageFooter>
                           <contents>
                              <table>
                                 <tableRows>
                                    <tableRow>
                                       <tableCells>
                                          <tableCell>
                                             <contents>
                                                <date>
                                                   <style>
                                                      <dataFormat>
                                                         <dateFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </date>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:left;width:25%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <pageNumber/>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:center;width:50%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <time>
                                                   <style>
                                                      <dataFormat>
                                                         <timeFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </time>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:right;width:25%"/>
                                             </style>
                                          </tableCell>
                                       </tableCells>
                                    </tableRow>
                                 </tableRows>
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="tb"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse;width:100%"/>
                                 </style>
                              </table>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="pf"/>
                              </defaultStyles>
                              <CSS value="padding-top:10px"/>
                           </style>
                        </pageFooter>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
            <classStyles>
               <classStyle name="pd_1" label="Excellent">
                  <CSS value="background-color:#009933; color:#FFFFFF;"/>
               </classStyle>
               <classStyle name="pd_2" label="Very good">
                  <CSS value="background-color:#FFFFFF; color:#009933;"/>
               </classStyle>
               <classStyle name="pd_3" label="Average">
                  <CSS value="background-color:#FFFFFF; color:#CC9900;"/>
               </classStyle>
               <classStyle name="pd_4" label="Below average">
                  <CSS value="background-color:#FFFFFF; color:#990000;"/>
               </classStyle>
               <classStyle name="pd_5" label="Poor">
                  <CSS value="background-color:#990000; color:#FFFFFF;"/>
               </classStyle>
            </classStyles>
         <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>

[arun_prs]

sorry juliaf,

                  My bad, when I run the tabular data end date showed up first and I overlooked it. Yes Its working Thanking you so much, I will test out in my actual. Thanks again