This content has been marked as final. Show 3 replies
I believe that your clients wants to see SRs that were created in the current year AND in the Daily, Last 30 days and YTD.
You logic is fine.
I first applied the filter on the opened date. The filter was YEAR("Date Opened".Date)=YEAR(CURRENT_DATE). This would fetch only SRs of the current year. To apply this formula, click on the fx button of Opened date and replace the exisiting value with YEAR("Date Opened".Date).
Then click on the filter of the column and Add session variable. In the Session variable add YEAR(CURRENT_DATE).
You need to use pivot table here. The rows would be the Type A, Type B etc.
The Columns would be the column having the function to check daily or last 30 days (refer below for the function) *@*
The measure would be # of Service Requests.
@CASE WHEN "Date Opened".Date = CURRENT_DATE-1 THEN 'Daily' WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Date Opened".Date, CURRENT_DATE-1) between 0 and 29 THEN '30 Days' ELSE 'YTD' END
Let me know if you were able to solve the issue. You can also call me at 91-8197839902, however please note that I am working in Indian Time Zone.
thanks for the help but
for the Year wouldn't this be more efficient? on the fiscal year field under Date Opened Category
Fiscal Year is equal to or is in NQ_SESSION.CURRENT_YEAR (session variable on fiscal year field)
with this you don't have to setup any other calculations inside the field formula
Also I tried out the formula you gave me with a Pivot table
if i only use the daily part then it shows the correct numbers
when i use 2 conditions Daily and 30 days - the daily number is correct but the 30 day number is total - daily (since CASE-WHEN works like IF-ELSE therefore this is expected)
and if i simply use YTD in else condition it returns wrong numbers.
the sumation of the 3 columns probably shows the correct total but not what i need
The reports has been developed. I have used normal count and sum method instead of the Case statement. Unfortunately I am unable to paste screen shots in here and I would be sending an email.
The logic is that when it is YTD, Last 30 days or Daily I consider is as numeric 1. Finally I do aggregation to get the sum. The aggregation can be done at column level (Step 1) or at Pivot Layout (Step 2) Level. I have done it at the Column (Step 1) level. You will find the details in the document.
I have also developed the same report using Combined Analysis. Let me know if you need the approach also, however, I would require a different communication system other than this forum and emails to explain that. I would be fine with Skype.
Please check your email for the details.
Edited by: Paul-CRMIT on May 18, 2011 12:08 AM
Edited by: Paul-CRMIT on May 18, 2011 4:20 AM