Using two calendars

[karensmith]

Our business calendar is based on Fiscal Year, but our users would like to view applications based on fiscal calendar or normal calendar year. Is this possible? Would we need to create two calendars? If yes, how will application objects access date range based on either calendar? The users would like to do monthly, quarterly, and yearly comparisions by either calendar. Does anyone with similar calendar requirments?

[#2]

Hi Karen,

a quicky could be to create an inline table.

LOAD * INLINE [
FiscalQuarter, Month
Q1, 4
Q1, 5
Q1, 6
Q2, 7
Q2, 8
Q2, 9
Q3, 10
Q3, 11
Q3, 12
Q4, 1
Q4, 2
Q4, 3
];

An other method is creating flags during script execusion like

if(Datum >= yearstart(addmonths(today(),-12)) and Datum <= addmonths(today(),-12),1,0) as isPrvYTD

Hope this helps a lot.

Rainer

[johnw]

Probably the simplest approach is to just have both sets of fields in one calendar - Fiscal Year, Calendar Year, Fiscal Quarter, Calendar Quarter and so on. Then for comparison, have two charts - one with a grouped calendar date dimension (cyclic or hierarchical), and one with a grouped fiscal date dimension.

As an alternative, rather than having one row for each date with both the calendar and fiscal calendar fields, have two rows for each date and include a Calendar Type field to distinguish between the two. Allow one and only one value to be selected for Calendar Type. One chart can then do the work of the two charts above, but the user can't select combinations of calendar periods and fiscal periods.

Both comparison ideas above assume that your users will manually select the periods they want to compare. Perhaps you just want to always compare the selected month, quarter or year to the previous month, quarter or year. Again, you could follow one of the above approaches for the data layout, but in the chart, use set analysis for the comparison.

Or maybe you always want to compare the previous month, quarter or year to the month, quarter or year before that. In that case, you could load up flags in your script to identify those periods, and use set analysis to ignore the selected dates, and just use your flags to display the desired data.

Lots of options depending on what you mean and how your users want to interact with the data.

[karensmith]

Thanks for your response John. I think I like your first recommendation. Created one calendar added Fiscal Year, Calendar year, date, Fiscal Quarter, Calendar Quarter. Seems to be working great.

I created a datamodel and linked our sales history detail to calendar file. I then linked another dataset(open orders) to calendar using date as the key.

But, now that all three tables are linked by date. I am creating synthetic links when trying to link Sales history directly to open orders. There are other common fileds, such as Customer, Product code, part number.

Any suggestions how to get all three tables link with creating synthetic links.

You help is greatly appreciated.

[karensmith]

John,

Please disregard my previous question. I was not creating application object correctly. Script was fine. Thanks again for your help.

[karensmith]

John,

Tested again...and synthetic keys are still a problem. I apologize for the confusion. Please advise on my previous response.

[johnw]

The date field is showing up in the synthetic key because it is shared between the tables, but so are a lot of other fields, so you'd have the synthetic keys regardless. The calendar is not the problem.

In this case, it sounds like your sales history and your open orders are really the same sort of information, and the difference is more like status - whether the order is open or closed. If so, I would join them into one table and include a status to distinguish between the two. That would eliminate the synthetic key.

If they are NOT the same sort of information, things get more complicated. You might then need to have different names for the fields, like "Historical Customer" and "Customer" so that they don't link up.

Or perhaps in this case, there is nothing wrong with having synthetic keys. They aren't ALWAYS bad, and I've intentionally created them before; they're just an indication that you should take a very close look at what you're doing.

[karensmith]

John,

Thanks for your response my question. Working on designing user interface objects.

I chose to your first recommendation with Calendar type and Show/Hide object. Seems to be working. I've created two charts for some of the objects, based on user's selection of calendar type.

Currently, we are not using any flags. I don't have much experience with flags. Are there any limitations on the number of flags used in a script?

For the most part users will make the time range selections, but we do have the need to for YTD calculations, but I'm stuck on how do this since we are using two calendar types.

Can you elaborate on this point :"Or maybe you always want to compare the previous month, quarter or year to the month, quarter or year before that. In that case, you could load up flags in your script to identify those periods, and use set analysis to ignore the selected dates, and just use your flags to display the desired data."

How do use set analysis to ignore the selected dates and use flags?

[johnw]

KarenSmith wrote:Are there any limitations on the number of flags used in a script?

Flags are just fields like any other. If there is a limitation on the number of fields, it's high enough that I haven't hit it and have never heard of anyone hitting it.

we do have the need to for YTD calculations, but I'm stuck on how do this since we are using two calendar types.

Just like you have fields for "Fiscal Year" and "Calendar Year", you could have fields like "Fiscal YTD Flag" and "Calendar YTD Flag". Use values of 1 and null for fastest processing speed in the charts.

Can you elaborate on this point :"Or maybe you always want to compare the previous month, quarter or year to the month, quarter or year before that. In that case, you could load up flags in your script to identify those periods, and use set analysis to ignore the selected dates, and just use your flags to display the desired data."
How do use set analysis to ignore the selected dates and use flags?

Let's say you want a report that compares sales for the current fiscal year to date with the previous fiscal year, same period. Let's say you want to take into account all current selections (like customer), but ignore the date-based ones. The expressions would look something like this:

sum({<"Fiscal Year"=,... "Calendar Month"=,...,"Fiscal YTD Flag"={1}>}Sales)
sum({<"Fiscal Year"=,... "Calendar Month"=,...,"Previous Fiscal YTD Flag"={1}>}Sales)

The "=" with nothing after it says to ignore selections in that field. So do it for every date field you have to tell it to ignore all date-based fields. It's much easier to tell it to ignore ALL fields, sum({1<"Fiscal YTD Flag"={1}>}Sales), but for the example, I'm assuming that's not what we wanted to do.

[karensmith]

John,

Thanks for your assistance. I was able to add my flags and tested results. I added YTD_FY and LY_YTD_FY and did the same gregorian years.

I then used flags in calculations. Sales vs Budget amount and percentage.

Regarding set analysis syntax?

This is your code:

sum({<"Fiscal Year"=,... "Calendar Month"=,...,"Fiscal YTD Flag"={1}>}Sales)
sum({<"Fiscal Year"=,... "Calendar Month"=,...,"Previous Fiscal YTD Flag"={1}>}Sales)

This is mine

=

num(sum({$<FYYear = {(FYYear)}, FYMonth = {(FYMonth},FY_YTD_Flag={1}>} SALESVALUE), '$#,##0;($#,##0)'))

What is wrong with my syntax? Trying to what you suggested when comparing a particular point in time this year compare to last year. How is the period of time controlled?