Interested to Buy Any Domain ? << Click Here >> for more details...
I have Date,product,Quantity_Sold columns and I want
Product, MTD(month Till date),YTD(Year Till Date) as output.
Can anyone help me with the logic in getting MTD i.e
quantity sold from 1st of this month to till date (i.e. 01-
11-2013 to 26-11-2013) and YTD i.e QTD sold from 1st date
of the year to till date(i.e. 01-01-2013 to 26-11-2013).
Sample output model:
Product MTD YTD
- 1 Answers
- 15539 Views
- L&T, I also Faced
- E-Mail Answers
Answer Posted / naresh
HI All,
Follow below steps to achieve this
1) In Filter condition place the below condition (to_char([START_DATE],'YYYY')=to_char({sysdate},'YYYY') and [START_DATE] <= {sysdate})
2)Create one Derived colum as MTD and in Expression use below code
case when (to_char([START_DATE],'MM')=to_char({sysdate},'MM') and [START_DATE] <= {sysdate})
then [Quantiry] end
Next in properties if this column for aggrigate Function and for Rollup Aggrigate Function change 'automatic ' to 'None'.
3) create one more Derived column as YTD and in expression use below code
case when (to_char([START_DATE],'YYYY')=to_char({sysdate},'YYYY') and [START_DATE] <= {sysdate})
then [Quantity] end
here sysdate is oracle date function.
Thats it .
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
how to create measures and dimensions?
New coloums are added in database, then how you know in report level
What is Online View?
What is metric store database?
What are the types of folder present in cognos?
Explain the difference between powerplay transformer and power play reports?
I have created a database "TestingDB" in MS SQL Server 2005. I have set the port properties and the Cognos Service is up. But I don't see anything in the Cognos. In the administration i have set the ODBC connection and it tested successfully.If i copy the samples to the appropriate directory then i can see the samples.But I don't know how to do the same thing with my own database "TestingDB". As i have configure the cognos in my company which is having a database (very near to production database).I am a novice in cognos. Please help me out.
What are the types of hierarchies?
What are the different uses of a repository manager?
i'm searching on cognos..i want to know real time scenarios..persons having real time exp or undertaking trainings or who searching on cognos...can send me the details...for proper guidence..for project and interview preparation..pls help me out..my mail-id is ammu2india@gmail.com..hope u will respond as soon as possible..thanks..
hi my name is siraj i want to know cognos queriy language
Can the expiration parameter of 365 in the cognos 8 configuration how to increase common symmetric key lifetime in days
What is meant by alternate drill down with ex?
What junk dimension contains?
What is cognos powerhouse?
