Forum

This content is now out of date.

Visit Our Community

How to combine 7 and July or month and year at the view level

Hi,

This is a another problem come from the same report.

Background:
http://www.yellowfinbi.com/YFForum-How-to-compare-the-value-of-same-month-in-different-year-?thread=205262#p2

Problem: I need to see the percentage of decrease of the number of no show/cancellation in 2014.07 to 2015.07.


Forum image


The separated month and year look ugly in the report.
So I want to combine month and year by making it " July 2014" at the view level. Another thing I want to do is to combine the number format of month and month name, making it " 07 July " (I want to do this because I don't want the month be displayed in an alphatical order when I use month name in the hierarchy)

I will appreciate that!

Hi Guest,

Looking at your report, it is hard to tell how exactly your date fields are configured (whether they are a part of a date hierarchy etc). There are a couple of methods I can suggest

1. Use an individual day date field (01/01/2015) and format it in MM yyyy format. Under this scenario you would only need to use a single field in the master query or sub query.

Here is an example of formatting an individual date field to appear in MM yyyy format:

Before


Forum image


After


Forum image


2. Use the text advanced function 'Concatenation' to combine both fields. Please keep in mind that when you use this function it doesn't respect field formatting so you would not be able to add a 0 prefix to your month field and then concatenate it to the year field.

Here is an example of the Concatenation advanced function:

Fields Before Concat


Forum image


New Concatenated Field


Forum image



Forum image


Anyway hopefully this information is helpful for you. Please let me know if you have any questions or concerns.

Kind Regards,

Dustin
Thanks a lot Dustin,

I tried the second way concatenate columns. The concatenate works perfect in the master query, but didn't work for the append query.(shown in the image below)


Forum image


Forum image


And I tried to apply the concatenation on month name with year, on month with year. But as long as it's in append sub query, it didn't give me anything.

Much appreciate!
Hi Guest,

Hmm that's odd that concatenation isn't working within your sub query...

Do you think that you could you send in a screenshot of your report output after trying to concatenate 'Append:Year' and 'Append:Month Name'?

Also, could you let me know which version of Yellowfin you are using so that I can test this in your build? I would like to test if this is a bug that has since been fixed.

Anyway, I look forward to your response and hopefully we can have a work around for you soon.

Kind Regards,

Dustin
Yeah it's weird. I copied the SQL to freehand SQL report, and it reported as error
"Invalid state, the Connection object is closed" I think our version is 6.3.

SELECT
T0.C0,
T0.C1,
T0.C2,
T0.C3,
T0.C4,
T0.C5,
T1.C4,
T1.C2,
T1.C3
FROM (
SELECT DISTINCT
datepart(year, "ClientVisit"."rev_timein") AS C0,
datename(month, "ClientVisit"."rev_timein") AS C1,
"Team"."team_name" AS C2,
"ClientVisit"."emp_name" AS C3,
CASE
WHEN 1 = 0 THEN "Planner"."visit_status"
WHEN "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD BY PROV', N'CNCLD>24hr') THEN N'Cancellations'
WHEN "Planner"."visit_status" IN (N'COMPLETED') THEN N'Completed'
WHEN "Planner"."visit_status" IN (N'NOSHOW', N'NOTPRESENT') THEN N'No Shows'
ELSE "Planner"."visit_status"
END AS C4,
COUNT("Planner"."visit_status") AS C5
FROM "dbo"."ClientVisit"
INNER JOIN "dbo"."Team"
ON (
"ClientVisit"."team_id" = "Team"."team_id"
)
INNER JOIN "dbo"."Planner"
ON (
"ClientVisit"."plan_id" = "Planner"."plan_id"
)
WHERE (
datepart(year, "ClientVisit"."rev_timein") = 2014
AND "Team"."team_name" IN (N'Med Clinic')
AND datepart(month, "ClientVisit"."rev_timein") IN (7)
AND "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD>24hr')
)
GROUP BY
datepart(year, "ClientVisit"."rev_timein"),
datename(month, "ClientVisit"."rev_timein"),
"ClientVisit"."emp_name",
"Team"."team_name",
CASE
WHEN 1 = 0 THEN "Planner"."visit_status"
WHEN "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD BY PROV', N'CNCLD>24hr') THEN N'Cancellations'
WHEN "Planner"."visit_status" IN (N'COMPLETED') THEN N'Completed'
WHEN "Planner"."visit_status" IN (N'NOSHOW', N'NOTPRESENT') THEN N'No Shows'
ELSE "Planner"."visit_status"
END

) T0
LEFT OUTER JOIN (
SELECT DISTINCT
"Team"."team_name" AS C0,
"ClientVisit"."emp_name" AS C1,
datepart(year, "ClientVisit"."rev_timein") AS C2,
datename(month, "ClientVisit"."rev_timein") AS C3,
COUNT("Planner"."visit_status") AS C4
FROM "dbo"."ClientVisit"
INNER JOIN "dbo"."Team"
ON (
"ClientVisit"."team_id" = "Team"."team_id"
)
INNER JOIN "dbo"."Planner"
ON (
"ClientVisit"."plan_id" = "Planner"."plan_id"
)
WHERE (
datepart(year, "ClientVisit"."rev_timein") = 2015
AND "Team"."team_name" = N'Med Clinic'
AND datepart(month, "ClientVisit"."rev_timein") = 7
AND "Planner"."visit_status" IN (N'CANCELLED', N'CNCLD>24hr')
)
GROUP BY
datepart(year, "ClientVisit"."rev_timein"),
"ClientVisit"."emp_name",
datename(month, "ClientVisit"."rev_timein"),
"Team"."team_name"

) T1
ON T0.C2 = T1.C0
AND T0.C3 = T1.C1










Sorry I forgot to screenshot the report output.

Report master query

Forum image

Append query

Forum image


filter

Forum image


Output

Forum image
Hi Guest,

Apologies for the delay in responding. I have good news and bad news regarding this case...

First, I was able to replicate what you are experiencing in 6.3. There appears to be a defect that exists surrounding the use of the concatenation advanced function within sub queries.

The good news is, this defect has been resolved in 7.1.

I think you have two options...

1. Upgrade Yellowfin to 7.1 and use the concatenate advanced function or a calculated field to combine your two date fields

2. Rethink your date formatting in your current report

I think #2 is likely the more reasonable of the two options in this case, as it doesn't seem to make much sense to have multiple date fields in your report when you could just reference an individual date field (i.e a specific day date) and format it into YYYY MMM format. Do you think that this is feasible?

Could you let me know if either work arounds will work for you? I look forward to hearing back.

Kind Regards,

Dustin

Hi Dustin,

Thanks for your help. But I don't think concerting the date to month and year will work in my current report since I need it to group by month. I tried to convert date to mm/yyyy, but the result of the report is grouped by date. I also tried to convert the format of month. But it doesn't work.

I need to solve the problem in a new and similar cross tab report. But I cannot even apply any advanced function in the columns and rows. The graph below sum up the same month in 2014 and 2015, giving me a wrong result.


Forum image



Forum image


So Now I'm thinking about doing the concatenation using freehand sql in the view level. I haven't figured the syntax though. Is there any easier to do this?

Thanks a lot again!

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.