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
- 15601 Views
- L&T, I also Faced
- E-Mail Answers
Answer / 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 |
What is power prompts?Can somebody explain me.
---------- Purpose of Factless Facttable?
what are the joins present in reportnet?which is most widely used?
If we run a report, that has 2 pages, i want to display one page in HTML format and another page wil display in pdf format. What is the procedure?
how can we Upgrad the reports from version Impromptu 6.0 to 7.0 and Power Play from version 6.6 to 7.0 ?
What is meant by alternate drill down with ex?
---------------Describe OLAP Reporting and RDBMS Reporting?
What is the difference between drill through, drill across?
what is an sid (surrogate key)?
Mention the difference between ‘macros’ and ‘prompt’?
how can create a report like if the salary>50,000 display balance,if salary<50,000 display green?
what is query subject
Teradata 
