I have two files, one is a Budget file by month for 2010 and the other is Actuals by month for 2010. So if we are at the end of April I will have Jan to April data in the Acutal table and my YTD espression is: Sum({$Month =>}amount) and it works fine. My question is how do I get YTD for the budget file which has 12 months of data but I only want Jan to Apr.
I'm afraid I don't understand. If you select Jan to April, you should only see Jan to April data? Ah, or are you not selecting? So you have all 12 months of 2010 in your budget file, but only Jan through April in your actuals file? So since you only have actual data for Jan through April, you only want budget for Jan through April? Do you EVER need the budget for months for which you don't have actuals? You could just avoid loading the other months in. If you DO want to sometimes see that budget, and just not in this particular chart, referencing just about anything from the actuals table should do the trick. If you have any field from the actuals table as a dimension, you shouldn't see budget for other months. If you have an actual amount, and you're comparing budget to actual, your budget expression could be if(sum(ActualAmount),sum(BudgetAmount)). Probably plenty of other ways. But yeah, I'm not sure I understand the question.
Sorry, should add that the user makes no selects. A sheet is opened by one person (the sheet is password protected) and they click a button. This button runs a macro that selects the appropriate month and year (which is put into a varialbles when the QV document script is reloaded using the peek function to find the maximum year and month in the Actual table) . The macro then emails an excel spreedsheet to a list of users. The list contains the users email address and the cost centers they are to get in the email. The Excel file has columns with current month actual, current month budget, YTD actuals and YTD Budget.
So what the button selects in this example is April and 2010. So, Actual YTD is achieved with the expression Sum( { $ Month =>} Amount), which ignores the month selection and gives the total in the Actual table (which is Jan to April). A similar expression for the Budget table would yield Jan to Dec when I want Jan to Apr.
John -
So you have all 12 months of 2010 in your budget file, but only Jan through April in your actuals file? YES
So since you only have actual data for Jan through April, you only want budget for Jan through April? YES
Do you EVER need the budget for months for which you don't have actuals? YES
OK, I think I understand now. I think you want this:
sum({<Month={"<=$(=Month)"}>} Budget)
The $(=Month) should execute first, returning the selected month. That gets literally inserted into the expression, resulting in this:
sum({<Month={"<=April"}>} Budget)
And I'm thinking that since Month is either a month() or a date()... right?... that the <= should work properly to give you January through April for the selected year. Then you can just sum the Budget. Hopefully it works.
I doubt if condition Month={"<=April"} could work properly, because you are comparing a Dual field Month with a string "April". If anything, you need to compare the numeric parts, something like this:
sum({<Month={"<=$(=num(Month))"}>} Budget)
For the purpose of creating YTD calculations, I usually calculate YTD Flags for my Calendar table - each Date (or Month) gets assigned a flag = 1 if the Date (Month) belongs to YTD period and null() if it doesn't belong. In a similar way, you can have flags for Prior YTD, Quarter-To-Date, Rolling 12 months, etc...
Once you have a flag, the Set Analysis condition becomes very simple:
Oleg Troyansky wrote:I doubt if condition Month={"<=April"} could work properly, because you are comparing a Dual field Month with a string "April". If anything, you need to compare the numeric parts, something like this:
Set analysis doesn't obey normal comparison rules. I believe it obeys list box search rules. But it looks like you're right, entering <=April in a search box doesn't work for a month() field, and neither does my set analysis expression. Strange. You CAN just put April in a search box, or 'April' in set analysis, and it works. So why not <=April? Guess it gets confused. OK, then. I was wrong. I expected it to be able to do more than it does.
Still, the expression works fine for how I normally define my months. My month fields are usually date(monthstart(Date),'MMM YYYY'). And set analysis IS smart enough to process "<=Apr 2010" with no problem, as is a search box. I'm not sure what the difference is, other than a date() vs. a month(), but it seems to be enough to confuse QlikView. Nope, that's not it. If it format the date as MMMM, it can no longer figure out <=April. I'm afraid I'm confused.
So I'm sorry for the misinformation. I appear to have been extrapolating a bit from what I do know, and I extrapolated to far.
With the "Flag" approach I had to modify Oleg format to be:
sum({<Month = , YTDFlag={1}>} Budget)
This is because in my example only April is selected which gets me the current month columns with correct data in the table. So in order to get the YDT columns to work, the part of the statement "Month = ," effectively removes the April selection and then only looks at data that has a flag equal to 1.
