Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman

Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman

Hi Logan, 

Count / Running-count / Total is all available only for Data Item Expression. What I'm trying to do is on the Report Expression specifically for the table list header. Inside the Report Expression there is no count or total function that I can use.

Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman

Hi Mohamad,

you have already unlocked the page layout and inserted multiple items into the list header. Why don't you set the source of one of them to Data item value and pick the desired calculation to show the value?

I then see a calculated value right in the header:

Hi Logan, 

Count / Running-count / Total is all available only for Data Item Expression. What I'm trying to do is on the Report Expression specifically for the table list header. Inside the Report Expression there is no count or total function that I can use.

Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman

Hi Robert,


I try changing from Report expression to Data item value. Yes it does give me the correct working days on the table list header, but somehow my table rows will be duplicated. 


I create separate Data Item to check the date according to date filter, another data item to get the current working days and another data item to get the last working day of month. But since i have two different date prompt, my table row will be duplicated. Instead of all is summed up according to the facility code, each row is unique with referencing to different dates. Is there any other workaround?

------------------------------
Mohamad Aiman Arif Mohamat Saat

Original Message:
Sent: Wed April 19, 2023 03:52 AM
From: Robert Dostal
Subject: Map Static Date Prompt to a Custom Table and Calculate the Working Days

Hi Mohamad,

you have already unlocked the page layout and inserted multiple items into the list header. Why don't you set the source of one of them to Data item value and pick the desired calculation to show the value?

I then see a calculated value right in the header:

------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen

Original Message:
Sent: Wed April 19, 2023 03:25 AM
From: Mohamad Aiman Arif Mohamat Saat
Subject: Map Static Date Prompt to a Custom Table and Calculate the Working Days

Hi Logan, 

Count / Running-count / Total is all available only for Data Item Expression. What I'm trying to do is on the Report Expression specifically for the table list header. Inside the Report Expression there is no count or total function that I can use.

------------------------------
Mohamad Aiman Arif Mohamat Saat

Original Message:
Sent: Wed April 19, 2023 12:39 AM
From: Logan Whitaker
Subject: Map Static Date Prompt to a Custom Table and Calculate the Working Days

Original Message:
Sent: 4/18/2023 10:16:00 PM
From: Mohamad Aiman Arif Mohamat Saat
Subject: Map Static Date Prompt to a Custom Table and Calculate the Working Days

Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman

------------------------------
Mohamad Aiman Arif Mohamat Saat
------------------------------

Hi Mohamad,

the next solution that comes to my mind is to use a singleton connected to a separate query. Unlock your page object and put it into the list headeer.
I tested it with your data as we also have a central date table with working day flag linked to all fact tables and it works.

My fact query calculates the amount like yours with an if that checks the date field with in_rage ?date? and work_day=Y. The second query has just one column "count(WORK_DAY)" and two filters: "WORK_DAY_FAL=Y" and "transaction_date in_ragen ?date?". The query pushed to the db will filter all dates from the fact table between the selected dates which are workdays. The result then just has to be counted or summarized depending on your data.

Can you try this approach?

Hi Robert,


I try changing from Report expression to Data item value. Yes it does give me the correct working days on the table list header, but somehow my table rows will be duplicated. 


I create separate Data Item to check the date according to date filter, another data item to get the current working days and another data item to get the last working day of month. But since i have two different date prompt, my table row will be duplicated. Instead of all is summed up according to the facility code, each row is unique with referencing to different dates. Is there any other workaround?

Hi Mohamad,

you have already unlocked the page layout and inserted multiple items into the list header. Why don't you set the source of one of them to Data item value and pick the desired calculation to show the value?

I then see a calculated value right in the header:

Hi Logan, 

Count / Running-count / Total is all available only for Data Item Expression. What I'm trying to do is on the Report Expression specifically for the table list header. Inside the Report Expression there is no count or total function that I can use.

Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman

Hi Robert,

Can you elaborate more on this? I would love to try this out.

Thanks, regards.

Hi Mohamad,

the next solution that comes to my mind is to use a singleton connected to a separate query. Unlock your page object and put it into the list headeer.
I tested it with your data as we also have a central date table with working day flag linked to all fact tables and it works.

My fact query calculates the amount like yours with an if that checks the date field with in_rage ?date? and work_day=Y. The second query has just one column "count(WORK_DAY)" and two filters: "WORK_DAY_FAL=Y" and "transaction_date in_ragen ?date?". The query pushed to the db will filter all dates from the fact table between the selected dates which are workdays. The result then just has to be counted or summarized depending on your data.

Can you try this approach?

Hi Robert,


I try changing from Report expression to Data item value. Yes it does give me the correct working days on the table list header, but somehow my table rows will be duplicated. 


I create separate Data Item to check the date according to date filter, another data item to get the current working days and another data item to get the last working day of month. But since i have two different date prompt, my table row will be duplicated. Instead of all is summed up according to the facility code, each row is unique with referencing to different dates. Is there any other workaround?

Hi Mohamad,

you have already unlocked the page layout and inserted multiple items into the list header. Why don't you set the source of one of them to Data item value and pick the desired calculation to show the value?

I then see a calculated value right in the header:

Hi Logan, 

Count / Running-count / Total is all available only for Data Item Expression. What I'm trying to do is on the Report Expression specifically for the table list header. Inside the Report Expression there is no count or total function that I can use.

Hi All,


We are using IBM Cognos Analytics with Watson (11.2.0).

Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).

From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 

This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

else (0)

I have unlocked the page to create a custom header. Can refer snapshot below as reference.



Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.

Any help is deeply appreciated. 



Thanks and regards,
Aiman