Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can someone tell me if they see something wrong with this formula?
Sum({$<ActualDate={'$(=Date(MaxPlanAsOfDate))'}>}ActualData)
When I hardcode the time period using the formula below, it works.
Sum({$<ActualDate={'2010-06'}>} ActualData)
Much thanks!
=Date(MaxPlanAsOfDate) is probably returning a full date and your working expression only has a partial. You probably need:
Sum({$<ActualDate={'$(=Date(MaxPlanAsOfDate, "YYYY-MM"))'}>}ActualData)
When using a dollar sign expansion, it is a good idea to create a straight table and add your expression without giving it a label. When the chart is rendered, the label will be your expression with the dollar sign expansion evaluated. For your example, you will see the value will be a different format than what you need based on your working expression.
Thank you. This didn't work - yet! However, I am trying to solve this for someone that I work with and may not understand the issue entirely. I am going to ask for additional clarification. It could be that you are correct. Thanks again! Ellen
1) When I had Sum({$<ActualDate={'2010-06'}>} ActualData) and '2010-06' is chosen, then the sum shows up. If I choose any other date, the sum is 0.
Then I used Sum({1<ActualDate={'2010-06'}>} ActualData). This sum seems to work when I choose dates from 2010-06 and later, but when I select 2010-03, for example, the sum is wrong. How do I get the sam sum no matter what date I select here?
2) Note that the dates mentioned above are string data type, not date data type.
I have left(Year(MaxPlanAsOfDate), 4) & '-06' shows up in the table to be '2010-06', but Sum({1<ActualDate={left(Year(MaxPlanAsOfDate), 4) & '-06' }>} ActualData) = 0 no matter what date I choose.
I even let a variable vDate = left(Year(MaxPlanAsOfDate), 4) & '-06' , then use
Sum({1<ActualDate={'$(vDate)'}>}ActualData), but it doesn't work.
What am I missing?
3) How do we deal with "out of virtual memory " error message?
Thank you for your help.
Hi Ellen,
It seems to be a question of formatting. Try:
Sum({$<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData)
Take care when comparing dates, as both field and variable/expression must be in the same format.
Hope that helps!
Thanks Miguel.
I tried your suggested solution
Sum({$<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData)
and the sum shows up when '2010-06' is selected, but if I select other dates, the sum is zero.
Then I tried, Sum({1<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData). It works for dates after 2010-06. If I choose 2009-09, the sum doesn't come out right. What am I missing?
Thanks again.
-Kim
Hello Kim,
Which different date formats do you store in MaxPlanAsOfDate field (or variable)?
Using "1" before the set modifier ignores the current selection and includes all possible values, so that's why you will see a lot of rows.
Regards
Hi Miguel,
In Excel, PlanAsOfDate column has values like 06/30/2010 with format mm/yyyy. Then I use the following load statement:
Load
date(max([PlanAsOfDate])) as MaxPlanAsOfDateActualDate is actually a TEXT (not date) column in Excel file with format yyyy-mm. It has values like "2010-06", "2010-09", and so on.
Thanks again,
-Kim
I see. So if you are loading max() then you group by some field? Have you checked with a listbox how many different records MaxPlanAsOfDate has?
Hi Miguel,
PlanAsOfDate has multiple values, but MaxPlanAsOfDate has only 1 value: 06/30/2010. Somehow,
Sum({$<ActualDate={"$(=Date(MaxPlanAsOfDate, 'YYYY-MM'))"}>}ActualData)
only works when ActualDate of '2010-06' is selected. When I select other dates, the sum is 0.
Is there any way the sum is the same no matter what ActualDate I choose?
Thanks much,
-Kim