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.
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?
Quote from: SGD on 01 Mar 2018 07:47:08 AM
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.
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.
Hi Lynn,
Quoteyou 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?
Quote from: SGD on 01 Mar 2018 08:32:32 AM
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:
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:
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.
QuoteDid 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:
[Reporting].[Report Date] = ?p_date?Thus all the query items are returning values for selected date.
QuoteWhat is the case statement expression you used?
CASE
WHEN ([Reporting].[Report Date] = _add_days(?p_date?,-1))
THEN ([Reporting].[Actual])
ELSE (0)
ENDSince 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.
Quote from: SGD on 02 Mar 2018 05:54:30 AM
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:
[Reporting].[Report Date] = ?p_date?
Thus all the query items are returning values for selected date.
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