Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis - Date filters

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!

Tags (2)
10 Replies
stephen-a_redmo
Valued Contributor II

Set Analysis - Date filters

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

Not applicable

Set Analysis - Date filters

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?

Not applicable

Set Analysis - Date filters

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

stephen-a_redmo
Valued Contributor II

Set Analysis - Date filters

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

Not applicable

Set Analysis - Date filters

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..

Not applicable

Set Analysis - Date filters

Hi there

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

Not applicable

Set Analysis - Date filters

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.

Not applicable

Set Analysis - Date filters

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.

Not applicable

Set Analysis - Date filters

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.

Community Browser