Something wrong in formula?

[bullish35]

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!

[Miguel_Angel_Baeyens]

Kim,

If MaxPlanAsOfDate has one only possible value, it makes sense to me that there is only one possible value that matches and so it will return data for those dates and not (because you are using set analysis to match only records with that date) the other dates.

If you want to sum regardless the selection done in ActualDate, use

Sum({< ActualDate = >} ActualData)

Hope that helps

[#3]

Hi Miguel,

I changed all of my date data fields to have a Date ("MM/DD/YYYY") format. For example, I loaded ActualDate as:

LOAD Date

(RIGHT(Date, 2) & '/30/20' & LEFT(Date, 2), 'MM/DD/YYYY') as ActualDate

Then I used your formula: Sum({<ActualDate={"$(=Date(MaxPlanAsOfDate, 'MM/DD/YYYY'))"}>}ActualData)

Again, this sum only shows up if I choose 06/30/2010. If I choose any other dates, the sum is 0.

What did I do wrong here?

Thanks again,

-Kim

[#4]

I may have been unclear before. I need to have the sum of ActualData at 06/30/2010 regardless of what ActualDate is selected (I should have the same number no matter what ActualDate is selected). Is there a way to do this in Qlikview?

Sum({< ActualDate = >} ActualData) will give me the sum of ActualData depends on what ActualDate is selected. If ActualDate = 09/30/2010 is selected, it'll give me the sum of data for 09/30/2010, not 06/30/2010.

num(Sum({$<ActualDate={'06/30/2010'}>}ActualData), '#,##0', '.' , ',') shows the right sum when ActualDate of 06/30/2010 is selected. If other dates are selected, I want to have the same sum too, but this sum is 0 when other dates are selected.

What did I do wrong here?

Thanks much,

-Kim

[#5]

Can you repost your message? It is being cut off and I can't tell what the remaining text says.

Your second expression seems to suggest that the expression works when the date is selected. I'm guessing that when the data is selected, this expression will also give that answer:

num(Sum(ActualData), '#,##0', '.' , ',')

Meaning that your Set Analysis isn't doing anything in that case. Since you are overriding your ActualDate selection in the Set Analysis, that should ignore the selection on that field and instead follow your Set Modifier. That isn't working in your expression, because the date must be selected for it to work. That means that the Set Modifier is not working. It is probably a formatting issue.

Create a List Box for your ActualDate field. What is the format of those dates in the List Box? Is it M/D/YYYY, MM/DD/YYYY, YYYY-MM-DD, etc? That format is very important in getting this to work. If it is 6/30/2010, 06/30/2010 will not work.

You should also check out the following post for some tips on adding code to your posts: http://community.qlik.com/forums/t/15789.aspx