Hi Guest,

Unfortunately, you cannot reference virtual table objects from another virtual table. This is because virtual tables are really designed to manipulate database data in order to create fields that don't exist at the database level.

In your specific example I think you might need to consider building a new table in your database. Then you could reference that table within a virtual table from within Yellowfin.

I know this isn't quite the answer you were hoping for. Apologies for any inconvenience this may cause. Please let me know if you have any questions or concerns.

Cheers,

Dustin

Thanks a lot for your response! I wish we could create a new table in the database! However, our vendor is taking full charge of the back end. All our reports are build in views.

So my another question is, can I apply MIN function to a time format metric? I tried MIN(date of service) to select the first of date of service, but it didn't work, although in SQL, it showed MIN(convert(datetime, convert(varchar(20), "Fiscal year date of service", 112), 112)) BETWEEN '20150401 00:00:00.0' AND '20150430 23:59:59.997'.

Background of this question: The purpose of the report is to count the number of clients who have their first intake deployment service in every month of fiscal year 07/01/2014 to 06/30/2015. The trick is that the first intake deployment service does not refer to their first intake deployment service in their lives, but the first intake deployment service in the fiscal year. It is like counting the number of new clients for intake deployment service every month, but they are not real new clients since they may have had that service before 07/01/2014.

To capture their first visits in that fiscal year, I created a virtual table which selects the time of visit for the fiscal year 07/01/2014 to 06/30/2015. Then inner joined this table to client visit based the same clientvisitID.

SELECT DISTINCT "ClientVisit"."client_id" , "ClientVisit"."clientvisit_id" , "ClientVisit"."rev_timein" as "Fiscal year date of service" FROM "dbo"."ClientVisit" WHERE ("ClientVisit"."rev_timein" BETWEEN '20140701 00:00:00.0' AND '20150630 23:59:59.997' )

Then I put min(rev-timein) in the filter of my report. But the min function did not work.

Do you have any other approach about this? I'll very very grateful for your help.

Hi Guest,

Thank you for the background information. It helps clarify what you are trying to accomplish.

Your last sentence stood out to me. You said:

Then I put min(rev-timein) in the filter of my report. But the min function did not work.

My question is...Is the report you are building a traditional drag and drop report from the report builder? If so, have you tried adding the rev-timein field as a filter, and setting the filter to equal 'Minimum'?

For Example, say I have two time fields (dd/MM/yyyy format) as seen below:





Then add that field as a filter and click 'Advanced Settings'. From the drop down, select 'Minimum Date':





The oldest date should then be displayed:





Does this solution work for you? If not could you elaborate on the previously mentioned sentence? Specifically, is this a calculated field you are trying to add to your report etc?

Anyway,

I look forward to hearing back. Have a great day!

Cheers,

Dustin

Hi Dustin,

Thanks a lot for your help. I saw the minimum date function in the report, but that's not what I want.

Attached 'fist date of service report structure. png' is the screenshot of my report. The reason I need to put min(rev-timein) (appears as min(date of service ) in the report is : I need to select the clients who have their first date of service during that fiscal year in a specific month.For example: I want the the clients who have their first date of service in April 2015, I will set the filter as 04/01/2015 between 04/30/2015.The report should give me the right results if the MIN function can select the first date.And the reason I created the virtual table which selects clientvisits that happened from 07/01/2014 to 06/30/2015 is to avoid this function select the very fist service in clients' lives.





Last Friday, I edited my a bit. Trying to figure if I can convert my timestamp format to numeric to make this report work (shown in fist date of service view.png). However, I don't know what kind of number I should put in the user prompt filter to match the date range I want.( eg: 01/01/2014-07/31/2015)











Thanks very much again for your help/

Cheers,

Cathy

Hi Cathy,

So I think I may have figured out a possible solution for you. Just as a heads up, questions like these should generally be referred to your ISV (Credible) as they are best positioned to help resolve issues like this.

Also, as it pertains to the second half of your post, I definitely do not recommend trying to convert your date field into a numeric field inside of Yellowfin. This is because there is no way to account for the date separators ('-', '/' etc).

With all that being said, I think you can accomplish what you are trying to achieve by using parameters linked to date filters.

This can be accomplished by:

1. Create two date parameters in your view:










2. Drag in the date field you would like to filter twice into the Filters area of the report builder along with the two new date parameters:





3. Set one filter to 'Greater than or Equal to Column' and set the other to "Less than or Equal to Column'. Link each field to a parameter:





4. You should now be able to filter on a specific date range as mentioned in your previous post:





5. The last step in your case would be to create a calculated field with the following syntax min(YourDateField) and drag that field into the report:





6. Navigate to the output step and test your parameters, they should return the minimum value of the date range selected:





There are a couple steps involved here, but hopefully this solution will work out for you.

Have a great day!

Cheers,

Dustin

Hi Dustin,

I tried your approach : put the calculated field min(fiscal year of service) in my filter at set it to the month I want. But this min function still did not select the first date for my report. Although the SQL shows MIN("Virtual Table"."Fiscal year date of service").














Then I tried to test the Min function in caculated fields. We can see from the pictures below min didn't select the first date.






However, it seemed to work after I delete the metric ' fiscal year of service'





So I'm really curious about how min function works >< There must be something wrong with my report structure or.... the brain of this function.

Thanks,
Cathy

Opps sorry. The pictures I attached was a mess. I'm re-posting it here.



I tried your approach : put the calculated field min(fiscal year of service) in my filter at set it to the month I want. But this min function still did not select the first date for my report. Although the SQL shows MIN("Virtual Table"."Fiscal year date of service").













But see how right is the SQL!:

SELECT DISTINCT
datename(month, "Virtual Table"."Fiscal year date of service"),
"Clients"."last_name" + ', ' + "Clients"."first_name",
MIN("Virtual Table"."Fiscal year date of service"),
"Virtual Table"."Fiscal year date of service"
FROM "dbo"."ClientVisit"
INNER JOIN "dbo"."Clients"
ON (
"ClientVisit"."client_id" = "Clients"."client_id"
)
INNER JOIN (
SELECT DISTINCT "ClientVisit"."client_id" as "Clients ID in fiscal year", "ClientVisit"."clientvisit_id" as "clients Visits ID in Fiscal Year", "ClientVisit"."rev_timein" as "Fiscal year date of service" FROM "dbo"."ClientVisit" WHERE ("ClientVisit"."rev_timein" BETWEEN '20140701 00:00:00.0' AND '20150630 23:59:59.997' )

) AS "Virtual Table"
ON (
"Virtual Table"."clients Visits ID in Fiscal Year" = "ClientVisit"."clientvisit_id"
)
WHERE (
"ClientVisit"."visittype" = N'Deployment Intake'
)
GROUP BY
datename(month, "Virtual Table"."Fiscal year date of service"),
"Clients"."last_name" + ', ' + "Clients"."first_name",
"Virtual Table"."Fiscal year date of service"
HAVING MIN("Virtual Table"."Fiscal year date of service") >= '20150401'
AND MIN("Virtual Table"."Fiscal year date of service") <= '20150430'




Then I tried to test the Min function in caculated fields. We can see from the pictures below min didn't select the first date.











However, it seemed to work after I delete the metric ' fiscal year of service'







So I'm really curious about how min function works >< There must be something wrong with my report structure or this function.

Thanks,
Cathy

Hi Cathy,

The min() function is a SQL function that returns the smallest value of a given column. There really isn't anything special about it as it pertains to how it's implemented in Yellowfin.

Here is a quick link outlining the min() function:

SQL MIN() Function

I'm a bit curious as to why you decided to select both date fields when originally creating your report? Do you need both date columns to be included? In terms of report design it may not be the greatest idea.

Also, which version/build of Yellowfin are you currently using?

Cheers,

Dustin

If you were talking about the first screenshort , I was simply following the example you gave me. So I selected both date fields. But yes I know it is not a good way from report design aspect.

I just came up with the reason: the t returns the smallest value of a given column.

So for a GROUP BY statement like:

Group by
datename(month, "Virtual Table"."Fiscal year date of service"),
"Clients"."last_name" + ', ' + "Clients"."first_name",
"Virtual Table"."Fiscal year date of service"
HAVING MIN("Virtual Table"."Fiscal year date of service") >= '20150401'
AND MIN("Virtual Table"."Fiscal year date of service") <= '20150430'

With this having statement,
The sequence goes like: it selects the first date of service of each clients in April, 2015 if they have visits in April. However, what I want is to select the first date of service in that fiscal year first, then find the clients who have their first date of service in April.

And I also figured the cause of the sames results of "First date of service" and "Fiscal year date of service" with the existence of fiscal year date of service in the report column: The group by "Virtual Table"."Fiscal year date of service" asked SQL to choose only the minimum value for each "Fiscal year date of service", and it just simply returned "Fiscal year date of service"

So I ended up just pulling out all the clients who had visits in the fiscal year and their first date of service during the fiscal year ("first date of service"). Then I sorted the "first date of service" in an ascending order. So it gives me results in an ascending month order. Then I exported the result to excel and do the rest of work. I hope I can find an easier way later. If you have a better way, please please let me know !!

Thank you very much for all your thoughtful responses again Dustin! That really helps a lot!

Cheers,

Cathy