How to display sales value for previous day in a list report

[cognovice]

I want to display a column in the list report that shows the sales value only for yesterday (previous day).  I have "value" as a sales fact within my package.  I also have sales invoice date as a date field.

[cognovice]

Sorry,  I want to add more.  I can put a filter something like invoice date = _add_days(current_date, -1) but I want this filter applied only to the value column as I also have other columns to display such as Value Month To Date and Value Year To Date.

[cognostechie]

Just ask the business to stop selling anything from today and simply delete all sales data prior to yesterday.  ;)


Actually, you are on the right track. Just apply that condition to the column and not to the query.

If ( [Date Column name]  = _add_days( current_date, -1  ) ) then (  [Sales value column name]    ) else (0)

[cognovice]

Hi Cognostechie,
I tried your suggestion however I am getting the attached error message.

[clamus17]

Hi Cognovice!

Upon checking your screenshot, you are applying your condition on a detail filter which will be applied on the whole report not on column Value. Try double clicking the column Value on your list or locate the Value Data Item in the list's query in the query explorer and apply the condition there.

[MFGF]

Hi Cognostechie,
I tried your suggestion however I am getting the attached error message.

Hi,

Delete the detail filter you are showing here, drag in a query calculation to your list, and use cogostechie's expression in your query calculation.

MF.

[cognovice]

Hi Clamus17 & MFGF,
Thank you very much for your suggestion.  It worked.  All good.

[cognovice]

Hi All,
I now need to add another column to this report that shows WTD (Week To Date) sales value.  When run on Monday morning, Shows the consolidated value for last week up to Sunday.  When run on Tuesday morning, it shows only Monday's value, when run on Wednesday, it shows Monday and Tuesday Value and so on.

Please help.

[clamus17]

Hi All,
I now need to add another column to this report that shows WTD (Week To Date) sales value.  When run on Monday morning, Shows the consolidated value for last week up to Sunday.  When run on Tuesday morning, it shows only Monday's value, when run on Wednesday, it shows Monday and Tuesday Value and so on.

Please help.

Hi cognovice,

Can you tell us what have you tried so far? I think there's a lot of date functions that you can apply here. To start with try using  _day_of_week function.

I also attached here a PDF file for common cognos date and time functions. Cheers! :)