I built a dashboard with Cognos embedded and added calculations to display percentages.
My challenge now is to find out a way to count distinct elements in a field based on one filter only, so that the value remains constant even if other filters are applied in the dashboard.

In fact, I have an asset with records related to people within a community across different Countries.
With calculations, I counted the population and then able to calculate percentages afterwards.
population = count ( distinct DSC_ba_csv.Issued_to_CNUM )

If I apply the filter on a specific Country, the dashboard correctly refreshes and displays Country percentages.

However, if I apply other filters in the dashboard, the population count gets recalculated accordingly, and the count of  population decreases whilst it should be constant ... I need it is calculated only based on the Country filter selected, and not vary if other filters are applied.

Any suggestion ? 

Pls help, thanks in advance


 ​

------------------------------
Massimo Loaldi
------------------------------

#CognosAnalyticswithWatson

Dear @Massimo Loaldi,

Filters coming from "All Tabs" and "This Tab" option can't be avoided.

However, if your FILTER option is available ( or can be made available may be in form of a LIST viz. item) on Dashboard Tab in form of a Widget :
Your requirement can be fulfilled by breaking the connections of your widget.

This will prevent filtering on calculated item when users make the selections/FILTERS using other widgets.

Hope this helps.

Steps to disconnect Calculated Widget from rest of the widgets :-

​

------------------------------
Dinesh K. Dhiman
BI Architect,
IBM Data and AI Experts Labs | India Software Lab Services
------------------------------

thanks @Dinesh K. Dhiman !
I did not know about the feature you mentioned and it is really useful in many cases​. In my case now, I feel I am almost there.
I anchored the widgets in purple below, and now they vary only when the Country filter in "This Tab" changes .. that is awesome.

On the flip side, proceeding this way, if I selected "Cloud" from the word cloud widget, the blue counters change, while the purple ones don't.
Ideally, the percentages should vary based on the new "# Employee w/badge" number. So, for instance, if there are 46 employees who gained the Cloud badges, then the percentage "% employee w/badges" should vary to 59,7% (46/77).

Is there any chance to create a calculation which varies based on a specific selection ?
Today I set the population = count ( distinct DSC_ba_csv.Issued_to_CNUM ) ... wonder whether there is the possibility to reference different calculation so that the population equals to the count distinct of CNUMs of the Country selected in "This tab" filters, and regardless other filters applied.
that way all connection will be "1", percentages would vary, yet the divisor (population) would be always the number of population of the Country selected in "This tab" filter.

Any suggestion?
thanks again for your reply


------------------------------
Massimo Loaldi
------------------------------

Original Message:
Sent: Sun July 19, 2020 01:58 AM
From: Dinesh K. Dhiman
Subject: Calculation in Cognos Embedded

Dear @Massimo Loaldi,

Filters coming from "All Tabs" and "This Tab" option can't be avoided.

However, if your FILTER option is available ( or can be made available may be in form of a LIST viz. item) on Dashboard Tab in form of a Widget :
Your requirement can be fulfilled by breaking the connections of your widget.

This will prevent filtering on calculated item when users make the selections/FILTERS using other widgets.

Hope this helps.

Steps to disconnect Calculated Widget from rest of the widgets :-

​

------------------------------
Dinesh K. Dhiman
BI Architect,
IBM Data and AI Experts Labs | India Software Lab Services
------------------------------

Hi @Massimo Loaldi,

I would like to understand your data first.
Please reach me on slack, once you are available.

Thanks
​

------------------------------
Dinesh K. Dhiman
BI Architect,
IBM Data and AI Experts Labs | India Software Lab Services
------------------------------

Original Message:
Sent: Sun July 19, 2020 04:26 AM
From: Massimo Loaldi
Subject: Calculation in Cognos Embedded

thanks @Dinesh K. Dhiman !
I did not know about the feature you mentioned and it is really useful in many cases​. In my case now, I feel I am almost there.
I anchored the widgets in purple below, and now they vary only when the Country filter in "This Tab" changes .. that is awesome.

On the flip side, proceeding this way, if I selected "Cloud" from the word cloud widget, the blue counters change, while the purple ones don't.
Ideally, the percentages should vary based on the new "# Employee w/badge" number. So, for instance, if there are 46 employees who gained the Cloud badges, then the percentage "% employee w/badges" should vary to 59,7% (46/77).

Is there any chance to create a calculation which varies based on a specific selection ?
Today I set the population = count ( distinct DSC_ba_csv.Issued_to_CNUM ) ... wonder whether there is the possibility to reference different calculation so that the population equals to the count distinct of CNUMs of the Country selected in "This tab" filters, and regardless other filters applied.
that way all connection will be "1", percentages would vary, yet the divisor (population) would be always the number of population of the Country selected in "This tab" filter.

Any suggestion?
thanks again for your reply

------------------------------
Massimo Loaldi
------------------------------


Original Message:
Sent: Sun July 19, 2020 01:58 AM
From: Dinesh K. Dhiman
Subject: Calculation in Cognos Embedded

Dear @Massimo Loaldi,

Filters coming from "All Tabs" and "This Tab" option can't be avoided.

However, if your FILTER option is available ( or can be made available may be in form of a LIST viz. item) on Dashboard Tab in form of a Widget :
Your requirement can be fulfilled by breaking the connections of your widget.

This will prevent filtering on calculated item when users make the selections/FILTERS using other widgets.

Hope this helps.

Steps to disconnect Calculated Widget from rest of the widgets :-

​

------------------------------
Dinesh K. Dhiman
BI Architect,
IBM Data and AI Experts Labs | India Software Lab Services

thank you @Dinesh K. Dhiman !
I appreciate you helped me fixing the issue. Country percentages work now, thanks to your tips on using average after adding Country totals in the original source.
The dashboard now works as expected and looks much better than before, thank you​

------------------------------
Massimo Loaldi
------------------------------

Original Message:
Sent: Mon July 20, 2020 01:25 AM
From: Dinesh K. Dhiman
Subject: Calculation in Cognos Embedded

Hi @Massimo Loaldi,

I would like to understand your data first.
Please reach me on slack, once you are available.

Thanks
​

------------------------------
Dinesh K. Dhiman
BI Architect,
IBM Data and AI Experts Labs | India Software Lab Services
------------------------------


Original Message:
Sent: Sun July 19, 2020 04:26 AM
From: Massimo Loaldi
Subject: Calculation in Cognos Embedded

thanks @Dinesh K. Dhiman !
I did not know about the feature you mentioned and it is really useful in many cases​. In my case now, I feel I am almost there.
I anchored the widgets in purple below, and now they vary only when the Country filter in "This Tab" changes .. that is awesome.

On the flip side, proceeding this way, if I selected "Cloud" from the word cloud widget, the blue counters change, while the purple ones don't.
Ideally, the percentages should vary based on the new "# Employee w/badge" number. So, for instance, if there are 46 employees who gained the Cloud badges, then the percentage "% employee w/badges" should vary to 59,7% (46/77).

Is there any chance to create a calculation which varies based on a specific selection ?
Today I set the population = count ( distinct DSC_ba_csv.Issued_to_CNUM ) ... wonder whether there is the possibility to reference different calculation so that the population equals to the count distinct of CNUMs of the Country selected in "This tab" filters, and regardless other filters applied.
that way all connection will be "1", percentages would vary, yet the divisor (population) would be always the number of population of the Country selected in "This tab" filter.

Any suggestion?
thanks again for your reply

------------------------------
Massimo Loaldi


Original Message:
Sent: Sun July 19, 2020 01:58 AM
From: Dinesh K. Dhiman
Subject: Calculation in Cognos Embedded

Dear @Massimo Loaldi,

Filters coming from "All Tabs" and "This Tab" option can't be avoided.

However, if your FILTER option is available ( or can be made available may be in form of a LIST viz. item) on Dashboard Tab in form of a Widget :
Your requirement can be fulfilled by breaking the connections of your widget.

This will prevent filtering on calculated item when users make the selections/FILTERS using other widgets.

Hope this helps.

Steps to disconnect Calculated Widget from rest of the widgets :-

​

------------------------------
Dinesh K. Dhiman
BI Architect,
IBM Data and AI Experts Labs | India Software Lab Services