Set Analysis - Date filters

[#1]

Hi everyone

I need some help on date filters through set analysis. Although I have read widely in the forum, I still cannot figure how to solve me problem.

My application contains one table called Chart of Accounts which have general ledger entries and posting dates. Budget entries are posted once a month. I created a calendar and linked the table to the GL posting dates. Posting dates are not consecutive so the table only pulls the dates from the calendar where postings are made.

When I make a chart showing actual vs budget expenses, I am supposed to calculate the budget YTD in this way:

1. Sum all budgets figures for one whole year eg 2009

2. Calculate the budget YTD by (Number of Days in Date Range Selected) / (Total Days in the Year) times the entire budget figure for the year. For eg for Jan to Apr and a budget of 12000, the Budget YTD would be 120/365 times 12000 = 3945.

My problem comes from the associative way QlikView pulls dates. I simply cannot figure how to get the selection called Number of Days in Date Range Selected and to put it in a Set analysis formula.

I have tried GetFieldSelections(Calendar_Month) but how do I convert the values to calculate number of days the user selects and use this number?

My set analysis currently looks like this:

SUM( {1 < [Calendar_Month] = {GetFieldSelections([Calendar_Month] >} Total [Calendar_YTD] }

Where [Calendar_YTD] is a boolean number field in the Calendar (0 or 1 depending on whether the current date is YTD)

Thanks in advance!

[stephencredmond]

Hi Jeanne,

Do you have a Calendar table? I.e. a table with one row per day, containing all the Year, Month, Day, Week, etc. values for that date?

If so, it would be a trivial matter to add a new row:

 ...
 1 as DayCounter

Then, either Sum(DayCounter) or Sum({ < Year = {$(=Max(Year))} > DayCounter) will give you a count of all selected days or all selected days in the latest year.

Stephen

[#3]

Hi Stephen

Thanks for your suggestion. I have a DayCounter called YTD (basically 0 or 1). The problem I have is that the application only pulls the dates associated the posting dates which means that it only counts the DayCounters with these dates and not with the whole calendar.

I worked out a fix yesterday which I must say is not elegant but allows me to see the date range the user selects. I took the MinString for the field called Month and the MaxString for the field and calculated the number of days the user selected. This would give me a "static" date range with which to do the budget calculations.

In this post, I would also like to find out from you or any one else in the forum how to initialize and use variables in set analysis expressions in charts. How do I write code like that in a chart ? Is it like this eg:

Let vSelected= GetFieldSelections(Month)
SUM( { 1 <Month={$(vSelected)} } Amount)

I always get an error - is it because I am missing a ; or is it because QlikView does not allow intialization of variables in a Chart expression?

[#4]

Hi Jeanne,

I normally use an input box formatted as a drop down list and then fed this into a variable. From there I pass the variable into my expression using pretty much the same logic as you have - see following sample.

SUM({<Month={$(MyReportingMonth)}>}Value)

I hope this is of some help.

Rod

[stephencredmond]

Hi Jane,

Given a max and min month, I might calculate the days between the start and end like this:

=Floor(MonthEnd(MakeDate(Max(Year), Max(Month),1))) - MakeDate(Max(Year), Min(Month), 1)

There is a problem with the way you are trying to pass the Month list because it is just comma separated while the Set will expect Quotes as well.

Stephen

[#6]

Hello,

I have a bit similar problem. I would like to make a single chart comparison between budget and actual sales per months.

The problem in here is that I've dates from two calendars, so if I choose the month dimension from budget calendar, it doesn't affect to the sum of actual sales and vice versa.

And i can't link the budget + actual calendars to master calendar and choose the month from there, because I also need the comparison by ledgers ( so I need to keep the link between budget and actual ledger transactions).

My tables are:

-Ledger info for all ledgers (Budget per ledger and Actual edger transactios are linked to this)
-Budget per ledger (Budget calendar is linked to this)
-Budget calendar
-Actual ledger transactions (Ledger transaction calendars is linked to this)
-Ledger transaction calendar

Thank you if you can think any kind of solution for me, haven't been using qlikview a lot yet..

[#7]

Hi there

That is certainly a useful solution and tip I could use in my later applications. Thanks!

[#8]

Hi Stephan

Thanks for your solution. My own solution came close to it except that I did not use the Floor() function before calculating the dates. My solution also checks for leap year and maps number of days to each month. Pretty long but I did not really trust myself to create another parallel calendar.

[#9]

Hi Jeanne!

did you have similar problems with your graphs and time? I didn't figure out any solution yet, how to make one graph (x values are months) from data which uses two calendars.

[#10]

Hi Jiir

My application also called for comparing budget with actual figures. I used this solution:

1. For each ledger transaction, I specified whether the transaction was "budget" or "actual"

2.. Linked transactions posting dates to the master calendar (both budget and actual)

3. When it was time to make the chart, I made a filter which took only budget or only actual eg. SUM( IF(BudgetName='Budget', LedgerAmount)).

I hope that helps. This solution calls for only one master calendar.