Difference of Total value of Data ITEM in Cognos Report Studio

[shital kumar]

HI Friends,

I want to find out difference of total of data ITEM in my Crosstab Report. Model is Relational. Please find attached image for clear picture.

Any help on this issue.



Thanks

[MFGF]

HI Friends,

I want to find out difference of total of data ITEM in my Crosstab Report. Model is Relational. Please find attached image for clear picture.

Any help on this issue.



Thanks

Hi,

The image you posted seems to suggest you have two items called QTY, each having different values? Is it the same measure nested below row headings we cannot see, or are they two separate measure values?

MF.

[shital kumar]

Hi,

These are not different measures, these are the same.
Both QTY and Amount are same measure and I need difference of Amount.


Thank you

[BigChris]

There's presumably another dimension to the report that you haven't shared with us...

[MFGF]

Hi,

These are not different measures, these are the same.
Both QTY and Amount are same measure and I need difference of Amount.


Thank you

If they are the same measure, why are they represented in two different rows? Are they nested below row headings you did not include in the attachment?

MF.

[shital kumar]

Hi MFGF,

Thanks for your immediate response.

Please find the below given attachment for clear picture of requirement.
Requirement is finding the difference of Amount from total value.



Thank you

[MFGF]

Hi MFGF,

Thanks for your immediate response.

Please find the below given attachment for clear picture of requirement.
Requirement is finding the difference of Amount from total value.



Thank you

Ok - so you have Product Lines as major row headings and QTY and Amount are nested below these? You then want the difference in the Amount value for two specific product lines? Did I get that right?

If so, you will need a couple of calcs in your nested rows to derive the Amount value for each of the two specific Product Lines, and you will need a calc below the Product Lines major row heading to derive the difference.

Drag a query calculation below your Amount row heading, give it a name (eg CE Amount), and use an expression similar to:

if ([Your Package].[Your Products query subject].[Your Product line item] = 'Camping Equipment') then ([Amount]) else (0)

Drag a second query calc below this, call it eg GE Amount, and use an expression similar to:

if ([Your Package].[Your Products query subject].[Your Product line item] = 'Golf Equipment') then ([Amount]) else (0)

Finally, drag a query calculation below the Product Line row heading, give it a name such as Difference, and use the expression:

total([CE Amount]) - total([GE Amount])

You should get a result that looks similar to the one below:



Cheers!

MF.

[shital kumar]

I have tried all the possible ways and also tried the way, which you suggested, but not able to resolve that.

Ok, anyway I have a different question regarding the same issue.
Can you plz check the attachment for the issue.




Thanks

[MFGF]

I have tried all the possible ways and also tried the way, which you suggested, but not able to resolve that.

Can you explain this? I put together an example with details of how to code your expressions, and included a screenshot of the results so you can see that it's a solution that works. Does it give you a different answer than the one you require? Can you explain in more detail what your requirements are? Can you explain what "not able to resolve that" means?

Ok, anyway I have a different question regarding the same issue.
Can you plz check the attachment for the issue.

Posting attachments is not a very effective way of communicating your requirements. You can see from the thread above that it took us several posts to be able to understand what you need when relying solely on attached images, and we're still unsure whether we have fully grasped your requirements based on what you have just posted. Please explain clearly in your post what you need to achieve so you get the best chance of someone being able to help you.

It looks like you're asking if you can replace the empty cells in your crosstab with zero values? This is an option available to you in the "Data format" property for your crosstab member fact cells. One of the options is labeled "Missing value characters", and if you enter 0 for this, it will replace the empty cells with zero.

MF.

[mahgar]

Hi MFGF,

I also have similar issue, I'm trying to subtract all the WITHDRAW from the rows and it's only working if there's a value inside of it. E.g Approved - WITHDRAW (90-17 = 73).
How to show in Data Item1 as Auto Withdrawn = 2 etc.

TIA