Previous Month sales repot with cancelled sale in 7day in same report

[newcognosbee]

Currently working on report where I need pull last month report(previous month) and top of it I have one more situation remove records below 7days based 'cancel'. I am new to Cognos
not quite where to apply conditions

I tried to apply in filter

if ([transacationdate = Last Month]) then
last_of_month(add_months({sysdate},-1))= _add_days([transacationdate] -14)

else
(null)

Thanks

[Ammus1234]

Try this filter:

([transacationdate] = [Last Month]) and (last_of_month(add_months({sysdate},-1))= _add_days([transacationdate] -14))

But I am not sure about the dataitems used like transacationdate, last month etc.

[newcognosbee]

Thanks Ammus, I really apprecite it.

I applied Filter condition on 'Sale_Date' or 'transactiondate' column,tried both options I am not sure where I am going wrong.any idea please


If ([Sale_Date]) then
last_of_month(add_months({sysdate},-1))<= _add_days([Sale_Date],-14)
else
(0)


error:
qe-def-0459
qe-def-0261 qfwp

one more condition

[Sale_Date] = last_of_month(add_months({sysdate},-1)) <= _add_days([Sale_Date],-14)

error
qe-def-0459
qe-def-0260
qe-def-0261 qfwp

[MFGF]

Hi,

Your filter should be a boolean expression that returns a TRUE or FALSE value for each row. You can AND or OR multiple expressions together. I would also recommend you use current_date instead of {sysdate} within your _add_days() function.

You haven't quite made it clear exactly what you are trying to get. Do you want results for all dates in the month prior to today's date (eg in September 2013 if you ran the report today) or do you want results for all days in the current month(eg in October 2013 if you ran the report today)?

Can you explain a little further?

Best regards,

MF.

[newcognosbee]

Thanks for your help but I still get it wrong,being a new to Cognos is taking time to figure out. I guess 'Sale_Date' is string column

My report is based 'Previous Month Sales' eg: Sep1 to Sep 30, and  customer who has Cancel/return the product with in 14days(basically include 14 day cancellations as well)


if ([sale_date])= true then
last_of_month(add_months(current_date,-1))<= _add_days([sale_date],-14)
else
(0)

this was also showing same error

[MFGF]

Hi,

Sorry - still not quite clear. You want all rows with a Sale Date in the previous month? An expression such as the following will do this:

[Sale date] between _first_of_month(_add_months([current_date], -1)) and _last_of_month(_add_days([current_date], -1))

This assumes [Sale_Date] is a date datatype. If it's a string or integer we will need a little extra work.

Can you clarify what "customer who has Cancel/return the product within 14 days" means? How do you record whether a customer has cancelled or returned a product? Do you have another date? Do you mean within fourteen days of the current date or fourteen days of the sale date? Or something else? Do you want to see ALL of September's sales plus cancellations from October? All of September's sales without the 14-day cancellations that happened in September? Something else?

Cheers!

MF.



Regards,

MF.

[newcognosbee]

Thanks MF, I guess I am dragging too much and given little info,my apologies. I will try to break down in bullet points. There are overall 2 requirements in this report.We have done first requirement

How do you record whether a customer has cancelled or returned a product?
It's recorded based on column called 'Status' example Status='Cancel' ,if the status=Active means it is in active

Do you have another date? Do you mean within fourteen days of the current date or fourteen days of the sale date? Or something else?
No there's no other Date field it will be based only on 'Sale_date' data (which is String field;-() >:()

[Sale_Date] is not date data type it's string. I tried to do in below manner by creating Data_Item,not sure it's best practice to get things done
cast([Sale_Date] as date)

Do you want to see ALL of September's sales plus cancellations from October? All of September's sales without the 14-day cancellations that happened in September? Something else?
Unfortunately Both filters data is based from from 'September data only. I know report requirement seems bit weird to me as well as it should been 2 reports

1.Filter 1=Select previous months data -([Sale date] between _first_of_month(_add_months([current_date], -1)) and _last_of_month(_add_days([current_date], -1))
2.Filter 2=We have another filter which will pull data of cancel policies with 14days on Status='Cancel' and  sale_date<=14 days

I though doing pretty standard way with 'If condition' but i am getting all sorts of errors

If Status='Cancel' then
last_of_month(add_months(Current_Date,-1))= _add_days([Converted_date_Sale_Dateitem], -14)
else
(0)

[MFGF]

Hi,

I think I've got it now. If your Sale_Date was a Date datatype, the filter you require would be something like this:

([Sale date] between _first_of_month(_add_months([current_date], -1)) and _last_of_month(_add_days([current_date], -1)))
or
([Status] = 'Cancel' and _days_between(current_date, [Sale_Date]) <= 14)

Note we don't use an if - then - else construct in a filter - an OR condition gives us what we need

Now all we need to do is to figure out how to convert your string date into a real date. Can you tell us how the string date is stored? Is it a yyyy-mm-dd format? Can you include an example?

Cheers!

MF.

Update. Another thought that occurred last night. Are you certain your date is a character field? It might be formatted to look like characters when it displays in a report even though it's in reality a date or datetime datatype. Right-click on the date item in your package tree (on the left) and select Properties. You should see the data type displayed there.

[newcognosbee]

Thanks once again MF. I really appreciate your help. Actually 'Sale_Date' is string,there's date column called which I found out later in research  'sale_date_id' which is date columns format '21 Oct 13 00:00:00'   

Thanks for the IF..condition I tried more ten 20 conditions and Thanks to Cognos error messages it doesn't help either

I have small query in your filter
([Status] = 'Cancel' and _days_between(current_date, [Sale_Date]) <= 14)

the result based on previous month and condition is Sale_date - 14days,if we do current date which will be out of context isn't:-(

Thanks

[MFGF]

Thanks once again MF. I really appreciate your help. Actually 'Sale_Date' is string,there's date column called which I found out later in research  'sale_date_id' which is date columns format '21 Oct 13 00:00:00'   

Thanks for the IF..condition I tried more ten 20 conditions and Thanks to Cognos error messages it doesn't help either

I have small query in your filter
([Status] = 'Cancel' and _days_between(current_date, [Sale_Date]) <= 14)

the result based on previous month and condition is Sale_date - 14days,if we do current date which will be out of context isn't:-(

Thanks

Hi,

Sorry - looks like I didn't quite understand your requirement. You said originally

2.Filter 2=We have another filter which will pull data of cancel policies with 14days on Status='Cancel' and  sale_date<=14 days

Can you describe the business logic here? What does 'sale_date <= 14 days' mean? I assumed you meant the sale date was within 14 days of today's date? You said you don't store a cancelled date. so what is the 14 days relative to?

Cheers!

MF.

[newcognosbee]

HI MF,

Apologies, couldn't update you yesterday as I was trying to fix the errors but seems little bit ran out solution ideas >:( >:(I appreciate your help

Thanks

[MFGF]

HI MF,

Apologies, couldn't update you yesterday as I was trying to fix the errors but seems little bit ran out solution ideas >:( >:(,if you don't mind can you please drop me test mail to my mail id then I will reply with report scenario and sample test data,since its confidential and my colleagues are on this site as well. I appreciate your help

Thanks

I'd rather not do this via email. If you can't explain the rules for the filter you need, send me a personal message on the site and we will try to figure out what it is you need to do. I just need to understand what the 14-day requirement is relative to - 14 days from when? Keeping the thread on the site gives others the chance to help too, and might help people with similar issues later on.

Cheers!

MF.