Compare the data for current month and last month

[kvchowdary]

Hi all,

I have a requirement to compare the data for current month and last month

below are the requirements

1)if data is same for all columns between current and previous months then we need to show as no change(in a separate column in report)
2)if data is changed then we need to show as changed and need to highlight which column data was changed)
3)if data is new then we need to show as new
4)if data was deleted then we need to show as deleted like that

Thanks,
vck

[hespora]

I'm trying to understand the requirement, and I'm failing miserably. Can you elaborate? Maybe create a mockup with some dummy data?

Incidentally, what kind of data are you analysing here? Purely dimensional, or do you have facts alongside that need to be monitored? If so, are those facts transactional in nature?

[BigChris]

I can picture what you're trying to get at, but I can't immediately think of a way of getting there. Hespora's question about the data is important...I can probably come up with something for relational data, but I'd be struggling with dimensional data.

[kvchowdary]

Hi Hespora and Bigchris,

Thanks for your responses,

Its a relational data and i am attaching the sample output of report,If some value is changed and we need to highlight that value in report as like in excel

cognos 10.2 version and oracle db

Thanks,
vck

[hespora]

I'm looking at the data, and I still don't understand it.

For the values you determined in 2nd through 6th rows, which you marked as "changed", "new", and "deleted" - just what in the example data determines that these are the correct categories? I don't see aything obvious.

[AnalyticsWithJay]

Ideally, this should be handled in ETL, but it's not difficult to accomplish in Cognos.

What database are you reporting against? There are functions that do this, like CHECKSUM, HASHBYTES, etc, but it depends on your vendor.

In summary, you want to do the following:

- Create two queries: CurrentMonth and PreviousMonth.
- In each query, create a data item and use a hash function to generate a hash key for the row
- Join the two queries in Report Studio using a full outer join.
- In your final query:
if [CurrentMonth].[Value] is missing but [LastMonth].[Value] is not, the record is deleted.
if last month value is missing, but currentMonth is not, the record is new.
if both are available, compare the hash value. If it's different, it's a changed record.

For highlighting changes, create your conditional styles and variables as you normally would.

Note: The join of the two queries is a local join handled by Cognos. Make sure you reduce the intermediate result sets to the absolute minimal number of rows if possible, to minimize the hit on performance.

Jay

[kvchowdary]

Hi hespora,

We need to show the combination of data for current and previous month,suppose if some thing got deleted in current month then we need to show as deleted,i have highlighted as opname ,PName got changed on this month,so we need to show what is changed in drill through report.In the main report we need to show what got chanaged

Thanks,
vck

[Invisi]

Usually I create a query for both periods to be shown or compared,
join them on all relevant other (non measure) columns,
put all columns from the main period in the join query,
put the measure columns from the other period also in the join query,
create the comparison data items in the join query.

for your join conditions, look at what you want to show. I see something like 'deleted', so probably you want a full outer join. That means that you have to make all context (= non measure) columns conditional, as they may have to be pulled from either of the two base queries.

[kvchowdary]

Hi Invisi,

I am not getting your point.

[Invisi]

which part of my explanation is vague to you?

[Seveman]

Hi vck,

besides making outer joins as other people have suggested to get all data for both periods, you need a data item, let's call it status, that will hold "deleted", "changed", "new" and "unchanged" values baed on a comparison of the month values such as:
if prev_month is null then "new"
else if new_month is null then "deleted"
else if prev_month is not null and prev_month <> new_month then "changed"
else "changed"

and then a variable to hold a value depending on the value fo status so that you can use a style variable or conditional formatting on prev_month and new_month columns to highlight the right column

[Seveman]

Srry typo in my post
else "changed"
shoudl be else "unchanged"

[kvchowdary]

Hi Seveman,

I have tried your method but i am not getting the values correctly,I did the below
I have created 2 queries one for current and one for previous month and i have joined(Outerjoin) based on id,after that i created the 30 data items in joined query and applied the below logic in dataitems
case when

 .[id] is null then 'new'
when [cur] .[id] is null then 'deleted'
when 
 .[id] is not null and 
 .[id]<> [cur] .[id] then 'changed'
else 'unchanged'
end
 i have written like this,i have tried to replace the values with data items like below
when 
 .[id] is null then[cur] .[id] like that,but i am not getting the expected result in final query

Thanks
vkc

[Invisi]

what are the query names for your current month and last month data? Basically the logic is:
1) if [current] exists and [previous] is null, then NEW
2) if [current] is null and [previous] exists, then DELETED
3) etc.

[kvchowdary]

Hi Invisi
Thanks for your reply
query names are current month and previous month and in report we have 50 columns.do i need to write this expression for all the columns and in final query how can i highlight if something is new,deleted ,no change like that

Thanks,
vck

[Invisi]

Comparing all columns in your report queries sounds like a bad idea. I would need more info about your situation to give the best advice. Normally I would solve a comparison with multiple fields with a hash. This is faster to compare. But I would do that in the data warehouse.

[kvchowdary]

Hi Invisi,

Thanks for your reply

here is my requirement

i need to compare the data between current and previous month in list report,i have 30 columns in my report..suppose if some thing is changed from previous month to current month then we need to add one more column to list and in that column we need to show as "Changed" and in the same column we need to highlight the column which was changed from previous to current(not column need to highlight the value).

and same if new data is added need to show it as "New",if something deleted need to show as "Deleted" like that


We have a id column to differentiate from previous month to current month.I have created 2 queries one for current and one for previous and i have joined both the queries with Outer join and i am trying to create the data items which i was mentioned earlier

Here i am strucking

Thanks,
vck