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

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

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

After

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

New Concatenated Field

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)

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.

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.

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?