11 Replies Latest reply: Feb 1, 2012 6:16 AM by Stefan Wühl RSS

    Sum from mindate to selected date

      Hi,

       

      I have a list of projects which all have different start dates. I would like to make a "project to date" sum that works with the calendar function, meaning that if the start period is OCT-10 and I select 2011-06 in the calendar, I would like to see the sum from OCT-10 to 2011-06.

       

      I have made a date out of period field OCT-10 (or similar) to look like 20101001 to include the calendar.

       

      Example:

      Project_IDDateInvoiceCost
      12010-10-01A500
      12011-03-01B200
      2

      2011-04-01

      C2500
      12011-05-01D400
      22011-05-01D500
      22011-06-01E1000
      12011-07-01F300
      12011-08-01G600
      22011-09-01H1500
      12011-11-01I100

       

       

      From above table,if I in the calendar select 2011-08-01, I would like the sum(Cost) for each project from it's min(Date) to selected date. The function needs to work in a pivot table and chart as well, I would love to be able to pivot the Project_ID field and expand it to show the detaild level. *Update* Forgot to add field Invoice as a detaild level that holds information of the cost.

       

      Any help would be appreciated!

       

      BR

      Robert

        • Re: Sum from mindate to selected date
          Stefan Wühl

          I think you could use a set expression like

           

          =sum({<Date= {"<=$(=max(Date))"}>} Cost)

           

          for this, in combination with a dimension Project_ID.

           

          See also attached.

            • Sum from mindate to selected date

              Hi,

               

              that worked perfectly fine! Thak you very much!

               

              I'm not very used to the set expressions yet. Thus, I'm adding yet another question to expand this model .

               

              In a new column next to the sum column you showed above, I would like to see the sum untill month before. If I select 2011-08-01 in your attached application, I get the sum 6000, and if I select 2011-07-01 I get 5400. I would like 2 columns, one showing the 6000 and one showing 5400.

               

              BR

              Robert

                • Sum from mindate to selected date
                  Stefan Wühl

                  Just add another expression:

                  sum({<Date= {"<=$(=addmonths(max(Date),-1))"}>} Cost)

                   

                  Regards,

                  Stefan

                    • Sum from mindate to selected date

                      Hi,

                       

                      it works in the test application, but in my build, the date format is 20110801 (instead of 2011-08-01). Is there a way to format the date in the set expression?

                       

                      The two expressions looks like this when selecting 20110901

                       

                      =sum({<Date= {"<=20110901"}>} Cost)
                      =sum({<Date= {"<=2011-08-01"}>} Cost)

                       

                       

                      BR

                      Robert

                        • Sum from mindate to selected date
                          Stefan Wühl

                          Date() function should do the job. Try

                          sum({<Date= {"<=$(=Date(addmonths(max(Date),-1),'YYYYMMDD'))"}>} Cost)

                            • Sum from mindate to selected date

                              The expression works when not selecting any month (it is second last months value), but when selecting, I get 0 value. Any idea?

                                • Sum from mindate to selected date
                                  Stefan Wühl

                                  Not yet...

                                   

                                  I understood that both expressions work with your sample data and only the sum until last month works not correctly with your data, the other one does, right?

                                   

                                  If you don't use a label in your expression, your will get the expression printed as label, but with the set expression evaluated (so you see the formatted date). If you select any month, what Dates do you see in the set expression? Do they seem reasonably (the format should match the format of your Date field)?

                                   

                                  Is anything different in your real data model compared to above sample (except Date format)? If so, could you adjust your sample to match it?

                                    • Sum from mindate to selected date

                                      Hi,

                                       

                                      both are working with the sample, but the latter one (month -1) does not work with my real data.

                                       

                                      =sum({<FADate= {"<=20111101"}>}FIXED_ASSETS_COST)
                                      =sum({<FADate= {"<=20111001"}>}FIXED_ASSETS_COST)

                                       

                                      Those are the 2 expressions I get when not adding a lable and selecting 20111101, where the first one is just fine and the second one is ok when not selecting a month. The date format seems to be just fine from what I can say.

                                       

                                      The only thing that comes to my mind is that there are 3 different tables (1 for calendar data, 1 for invoice data and 1 for general project information data). However, since it's in the expression and not the script, this shouldnt affect (?), and both expressions are ok, eventually.

                                       

                                      BR

                                        • Re: Sum from mindate to selected date
                                          Stefan Wühl

                                          Sorry, no idea yet. Could you upload a small sample?

                                          (Upload is available in advanced editor)

                                            • Re: Sum from mindate to selected date

                                              Hi,

                                               

                                              I got a bit closer, but don't know how to solve the problem. Attached is a picture (I will try to upload a sample if needed later).

                                               

                                              First column should always show max cost of the fixed asset not matter what selections are done. Second column should show total cost to selected period. Third column shows total cost to selected period - 1, and fourth column should just show the change between column 2 and 3. What I've asked for is column 2 and 3.

                                               

                                              Anyhow, there are 3 tables, as described above. If I select a period/date from field (reffering to attached pic) "PERIOD_NAME" or "FADate", which both are found in the general infromation table, column 2 is just showing current month value and column 3 is showing 0. I don't mind this since I'm not planning to present either of those fields.

                                               

                                              If I select in "Year Month" which is the calendar table, I get the same as above, current month and 0.

                                               

                                              If I select from "EffectivePeriod" which is in the invoice table, I get the same again.

                                               

                                              However, If I select from "EffectiveDateFromPeriod", which is also in the invoice table, the two expressions are working perfectly fine. This field is also the timefield used in the calendar and the link between those tables.

                                               

                                              Any idea how I can get the calendar working instead of the "EffectiveDateFromPeriod" field? No matter where I select in any of the mentioned fields above, Qlikview reduces the data in "EffectiveDateFromPeriod", basically passively selecting in that field. The expressions are however only working when actively selecting in that field.

                                               

                                              BR

                                                • Re: Sum from mindate to selected date
                                                  Stefan Wühl

                                                  I believe you need to clear all fields that the user might select in and that interfere with EffectiveDateFromPeriod:

                                                   

                                                  sum({<Date= {"<=$(=EffectiveDateFromPeriod (addmonths(max(Date),-1),'YYYYMMDD'))"}, [Year Month]=, EffectivePeriod=, PERIOD_NAME=, FADate= >} FIXED_ASSETS_COSTS)

                                                   

                                                  edit:

                                                  corrected some field names, but please double check!