Yesterday data calculation

[SGD]

Hi,

I am using Cognos Analytics Active Reports.

I have a report query which filters based on selected date.There are 3 fact values like Actual, Target, Goal which are getting calculated correctly. However, I need to calculate same fact values in the same query for yesterday for which I am using below report expression. Date prompt excepts single date selection to pass it on to report query.

Code Select Expand

total ([Actual] for _add_days (?prm_date?,-1))

Above expression doesn't work as expected since it is returning me selected date's value instead of Selected Date - 1 i.e. Yesterday's value.

Could you please someone assist to get the correct values in this scenario or verify the applied logic?

[Lynn]

Hi,

I am using Cognos Analytics Active Reports.

I have a report query which filters based on selected date.There are 3 fact values like Actual, Target, Goal which are getting calculated correctly. However, I need to calculate same fact values in the same query for yesterday for which I am using below report expression. Date prompt excepts single date selection to pass it on to report query.

Code Select Expand

total ([Actual] for _add_days (?prm_date?,-1))

Above expression doesn't work as expected since it is returning me selected date's value instead of Selected Date - 1 i.e. Yesterday's value.

Could you please someone assist to get the correct values in this scenario or verify the applied logic?

You are filtering the query for the selected date which means the only results coming back from the database will be for that date. You are excluding data for all other dates and it is, therefore, impossible to compute anything for other dates because data isn't in the result set.

You will need to modify your filter to bring data back for both dates and then adjust everything else accordingly. For example, you can create a query item for Actual and Yesterday Actual and bucket the figures using a case statement. There are other methods such as using a query reference to get the details from one day or the other into separate queries if that better suits your requirements. It is difficult to know what adjustments you'll need without knowing more about how you are trying to report this information.

[SGD]

Hi Lynn,

you can create a query item for Actual and Yesterday Actual and bucket the figures using a case statement.

I have already tried with Case statement but even that is also not working. As you said, 2 query items are created in the same Query Subject but both returns same values.

Is there any other alternative to achieve this functionality within same query?

[Lynn]

Hi Lynn,

I have already tried with Case statement but even that is also not working. As you said, 2 query items are created in the same Query Subject but both returns same values.

Is there any other alternative to achieve this functionality within same query?

Did you change your filter to include both dates?

What is the case statement expression you used?

I'd expect to see an expression for your selected date actual metric as:

Code Select Expand


case
  when [Your Date] = ?prm_date?
      then [Your Metric]
  else 0
end


And I'd expect to see an expression for your yesterday date actual metric as:

Code Select Expand


case
  when [Your Date] = _add_days( ?prm_date?, -1 )
      then [Your Metric]
  else 0
end


Then you can total one or the other without any need to use a "for" scope.

[SGD]

Did you change your filter to include both dates?

If I add Yesterday's date in the filter then won't report query will show incorrect results? Currently I am using Date filter as below:

Code Select Expand

[Reporting].[Report Date] = ?p_date?

Thus all the query items are returning values for selected date.

What is the case statement expression you used?

Code Select Expand

CASE
WHEN ([Reporting].[Report Date] = _add_days(?p_date?,-1))
THEN ([Reporting].[Actual])
ELSE (0)
END

Since WHEN condition never satisfies, it always goes to ELSE part and shows 0.

Top of that I need to apply one more calculation i.e. (Today_Actual - Yesterday_Actual)/Yesterday_Actual.

[Lynn]

If I add Yesterday's date in the filter then won't report query will show incorrect results? Currently I am using Date filter as below:

Code Select Expand

[Reporting].[Report Date] = ?p_date?

Thus all the query items are returning values for selected date.

Code Select Expand

CASE
WHEN ([Reporting].[Report Date] = _add_days(?p_date?,-1))
THEN ([Reporting].[Actual])
ELSE (0)
END

Since WHEN condition never satisfies, it always goes to ELSE part and shows 0.

Top of that I need to apply one more calculation i.e. (Today_Actual - Yesterday_Actual)/Yesterday_Actual.

Change your filter to include both dates. You will never (ever, ever, ever) be able to report on both dates within a single query unless you expand your filter to include both dates.

Once you've done that, create a query calculation for "today actual" and another query calculation for "yesterday actual". Use a case statement as I've already explained such that figures for today's date go only into the today actual calculation and figures for yesterday go only into the yesterday actual calculation.

Then expose these figures in your layout as needed. You won't be using the Actual figure in your layout. You'd be using the today and yesterday figures instead as needed.

Once you have those two query items you can easily create a third query item that calculations the % change.

To help explain the concept, imagine you have only two rows of data returned from your query after you expand the date filter to include both yesterday and today:

Date            Actual
2018-02-02 100
2018-02-01 105

Once you add in the additional query calculations for today and yesterday:

Date            Actual Today Yesterday
2018-02-02 100     100    0
2018-02-01 105     0        105