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
- 15365 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
What are the advantages of shortcuts?
Can you define drill down and drill up?
how can I test reports in cagnos?
What project,model,parameter maps,namespace and database definitions....... what these are all contains? what are differences b/w them?
Can you define transformer?
Explain about scorecarding?
Does anybody have the sample of delivery document of cognos, or provide some link of websites Thanks
Explain the difference between powerplay transformer and power play reports?
Can you define report item?
What you mean by drill across and what is the difference between drill through, drill across?
Please Answer this Questions: 1.In FWM We have to create Hierarchies, Condition is we don't know the correct levels in the Hierarchies, in this case how can we find out the levels (year->Quarters->month->day)in data? 2. In FWM Standalone Filter is for Reusable purpose & Embedded Filter we cant resuse.but is this possible to convert the Embedded filter to standalone filter? 3.What are the differences between Relational Database Model & Multi Dimensional Database? 4.In Drill Down/ Drill Up, How to specify the limits for example in a report there is 6 Drill down is possible, but i want only 3 Drill downs, the report should not allow 4 Th Drill Down.How to make this ? 4. What are the limitations in Select and Search Prompt ?
Diff b/w Crossproduct and Outerjoin?
Can you define a catalog and types of catalogs in cognos?
What is called cognos reporting tool?
What do you understand by the cognos?
