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?

[johnw · Accepted solution]

num(sum({<FYYear=,FYMonth=,FY_YTD_Flag={1}>} SALESVALUE),'$#,##0;($#,##0)') // This year fiscal YTD
num(sum({<FYYear=,FYMonth=,LY_FY_YTD_Flag={1}>} SALESVALUE),'$#,##0;($#,##0)') // Last year fiscal YTD

When I showed the comma immediately following the equals sign, that wasn't shorthand. I meant to actually put the comma immediately after the equals sign. That tells QlikView to IGNORE any selections in FYYear and FYMonth. Instead, it will use only the FY_YTD_Flag to identify the time period you are interested in. Put another way, the expression changes from controlling the period of time using selections to controlling the period of time using a flag.

I also suspect you'll need more fields in the list. The idea is that you need to list ALL date fields, since you want to ignore them all. So you don't just need to ignore the fiscal year and month, but also the calendar year and month, dates if you have them, weeks if you have them, quarters if you have them, whatever you have.

However this only works for YTD using the current date. If you actually want to SELECT a particular point of time this year, then compare year through the same date last year, that's a little more complicated and can't be done with flags. It's doable if that's what you want instead, though.

[karensmith]

John,

I used your code below and added all my date fiedls and it worked great. It is amazing how set analysis can do so much. Thanks alot...

=

num(sum({<FYYear=,FYMonth=,FiscalQuarter=,INVDT=, INVOICE_DATE=, CalYear=,CalendarQuarter=,MONTHYEAR=,CalendarMonthName=,FYMonthName=,FY_YTD_Flag={1}>} SALESVALUE),'$#,##0;($#,##0)'

Regarding your last statement. We have one group of users that would like to see comparison current year to last year in a particular time. I have posted a question today on how to this.

I also replied to one of your forum post. the set analysis statement in your qlikview example works great when based on gregorian calendar. Is there way to accomplish this based on fiscal year?

(

sum({$<FYYear = {

$(=max(FYYear))}, FYMonth = {"<=$(=max({<FYYear={$(=max(FYYear))}>} FYMonth))"}>} SALESVALUE), '$#,##0;($#,##0)')

num

(sum({$<FYYear = {

$(=max(FYYear)-1)}, FYMonth = {"<=$(=max({<FYYear={$(=max(FYYear)-1)}>} FYMonth))"}>} SALESVALUE), '$#,##0;($#,##0)')

h

ttp://community.qlik.com/forums/p/18688/72388.aspx#72388

My thinking I was that I would be able to use this when calculating YTD, MTD, QTD etc... but just doesn't work out for April- March calendar. would year2date work?

[#4]

This is a useful thread. I have a related question that hopefully someone can help with.

I have created a number of flags in my load script eg. DateToday, DateYesterday, DateCurrentWeek, DateLastWeek etc. which assigns a 1 to any date that matches the criteria. That bit was fairly easy. Now, I want to have some action buttons in my app that when pressed will drive the selections in my calendar. So far I have:

=only({1<DateToday = {'1'}>} Date) which selects todays date

=only({1<DateYesterday = {'1'}>} Date) which selects yesterdays date

These both work fine but applying the same theory to the current week or last week using 'only' won't work.

Can somebody please suggest a solution to this?

Many thanks.

[johnw]

It's actually much simpler than you're thinking, even if the syntax is a little weird:

='=DateToday'
='=DateYesterday'
='=DateThisWeek'
='=DateLastWeek'

But regardless, I may be missing something very basic. So far as I know, there's no way to clear a specific field using actions. You can either clear everything or clear everything but a single field. And the previously-selected values interfere with making selections. So when I select last week from scratch, everything works great. But then when I try to select dates from this week, the first press of the button CLEARS the date field, and the second press makes the selection. And if I've already selected a day this week, and then press the button to select the whole week, nothing happens.

I can do it with macros, but that's lame.

How are people getting around this? Or are people still using macros for everything? I personally try to use actions these days, but I also hate them since my understanding is that they execute in parallel instead of in sequence, and since we seem to be missing very basic action functionality like being able to clear a single field.

Attached has both action and macro solutions.

[#6]

Hi John,

You're right - the syntax certainly is a little weird! As you state, basic functionality for actions is missing at the moment. I couldn't understand why a series of actions weren't running in sequence so you must be right that they run in parallel which is strange functionality.

In my example above, using a 1 instead of $ in the set has the effect of overriding previous/conflicting date selections so this would overcome the problem of having to press the button twice (once to clear previous date selections and once to select the desired date), if it were not for the fact that the "only" will not select all of the dates from DateLast Week or DateCurrentWeek.

Thanks for the macros, I think I will use them seeing as there doesn't appear to be a solution using actions.

Many thanks.

[#7]

John,

Having applied the macro solution, I see that executing them via an action button will not override conflicting date selections. For example, I also have Month as a dimension, so if May is selected from Month and I try to execute the macro for yesterdays date, nothing happens. Is there a way of modifying the macro to correct this?

Thanks.

[#8]

John,

I've tried modifying the macro as follows:

sub TodaysDate

set field = activedocument.fields("Date")

set field = activedocument.fields("Month")

set field = activedocument.fields("Weekday")

set field = activedocument.fields("Day of Month")

select "=DateToday"

end sub

but now it doesn't work. Any ideas?

[#9]

hi John,

I've modified the macro and it seems to work fine now.

Perhaps you can help me with understanding how it works? DateToday is a flag which assigns a 1 against todays date. But how does select "=DateToday" in the macro know to select todays date without specifying the 1?