13 Replies Latest reply: Nov 1, 2010 11:00 AM by Neil Miller RSS

    Something wrong in formula?

    Ellen Blackwell

      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!

        • Something wrong in formula?
          Neil Miller

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

            • Something wrong in formula?
              Ellen Blackwell

              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

                • Something wrong in formula?
                  kim.mai.9

                  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.

              • Something wrong in formula?
                Miguel Angel Baeyens de Arce

                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!

                 

                  • Something wrong in formula?
                    kim.mai.9

                    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

                      • Something wrong in formula?
                        Miguel Angel Baeyens de Arce

                        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

                          • Something wrong in formula?
                            kim.mai.9

                            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 MaxPlanAsOfDate

                             

                            ActualDate 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

                             

                              • Something wrong in formula?
                                Miguel Angel Baeyens de Arce

                                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?

                                  • Something wrong in formula?
                                    kim.mai.9

                                    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
                                      • Something wrong in formula?
                                        Miguel Angel Baeyens de Arce

                                        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

                                          • Something wrong in formula?
                                            kim.mai.9

                                            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





                                             

                                            • Something wrong in formula?
                                              kim.mai.9

                                              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

                                               

                                                • Something wrong in formula?
                                                  Neil Miller

                                                  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