Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Creating a Compute in a Star based on count of Days

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Creating a Compute in a Star based on count of Days
 Login/Join
 
Member
posted
I am in the process of creating a star and have a series of compute fields I need to build like ‘Qty per Day’, ‘Tons per Day’, etc.
The ‘Qty’ or ‘Tons’ fields are already available in the fact. The ‘Day’ needs to represent the ‘count of the days’ based on the users query or report as pulled from the Date dimension in the star.

If the user runs the report for a month then ‘Day’ in the star’s compute is the total number of the days in that month from the Date dimension in the star.

If the user select separate dates (say 4/12/2016; 4/15/2016; 4/20/2016) then ‘Days’ would be 3. And the compute for ‘Tons per Day’ in the star would be ‘Total Tons for the 3 Days specified’ divided by 3 (count of the dates selected); even if one of the days has NO tons in the fact.

Everything I have tried thus far either adjusts ‘Days’ based on which days had values or based on total count of the other sort fields in my test reports.

DATADIFF will not work because I can’t be assured users will select a string of dates.

The old SSAS cubes the users are being transitioned from handled this easily.

I am trying to provide similar fields the users have today and not make them do additional calcs in IA or Quick Data.

Any direction or examples would be much appreciated. Thanks.

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8.009
Windows, All Outputs
 
Posts: 13 | Registered: January 15, 2014Report This Post
Virtuoso
posted Hide Post
How do they select the dates and what are the options they have to perform such selection ?

Knowing that may help us trying to gives you a solution 'cause with Focus, there is always (most of the time) more than one answer.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Users would mostly select the dates as filter on the DATE table in the Star/Business View.
The Star is fairly simple; 1 fact and about 10 dimensions.

The DATE dimension fields are typical; Full_Date, Month, Year, Accounting Month, Accounting Year, Week Number (in year), Current Day (0=today, 1=yesterday, 2= two days ago, etc.), Current Month, Current Year, etc. Users could select or combine any of these date fields as a filter. So the Compute in the master file will need to determine the total number of days selected in the query and pass that value to the 'DAYS' denominator of the master file compute.

I am thinking, since the users could be very creative in their report and filtering determining the total days used in the Compute need to be at the lowest level (every day = 1 and just total up the dates selected.)

As a side note:
The system being replaced is an SSAS cube where the designer (not me) used the Date table as both a Fact & Dimension and had a field called DAYS (with a value of 1 for each calendar day) the would be Total based on the period the user selected in the pivot. So even if the user selected dates that were not in a continuous range the Total Days used by the SSAS Field calculation was correct since it added up the number of days select.

Hope this provides some additional details. Thanks.
I need to find a way to just total the number of days select from the DATE table independent of the values and other attribute data.


WebFOCUS 8.009
Windows, All Outputs
 
Posts: 13 | Registered: January 15, 2014Report This Post
Master
posted Hide Post
What about using Count Distinct on the Day? esentially thats what you want isn't it? Unfortunately you won't be able to use count distinct on any other field... but it would work in the master.

Your date table should also be the parent table for your master join. and everything else should be a left outer. that will ensure that you don't lose any date values due to data not existing in the table.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Creating a Compute in a Star based on count of Days

Copyright © 1996-2020 Information Builders