13 Replies Latest reply: Mar 11, 2014 11:37 AM by David Decarre RSS

    Year2Date function

    Josetxo Amonarriz

      Hello,

      well, this is my first post in the Qlik Community.

      We have recently purchased the product and our partner has made most of the implementation. Anyway, we would like to do some minor changes / improvements.

      One of them is this one: we have a chart that shows this year's (Year2Date) sales information versus last year's (LastYear2Date) Sales Information:

      =Sum(If(year2date(DateTransactionID),SalesAmount)) //This is Current Year's info

      versus

      =Sum(If(year2date(DateTransactionID,-1),SalesAmount)) // This is Last Year's info (until current date)

       

      The improvement is that I would like this information to be filtrable: if I select 2007 for the year in the filters, I would like this chart to show 2007 and 2006 information. This does not work with the current formula, so I tried to change it this way:

      =Sum(If(year2date(DateTransactionID,Max(Year)-CurrentYear),SalesAmount))

      // This is Current Year's info, because Max(Year)-CurrentYear = 0 for 2010 (and -1 for 2009, etc...)

      VERSUS

      =Sum(If(year2date(DateTransactionID,Max(Year)-CurrentYear-1),SalesAmount))

      // This is Last Year's info (until current date) because Max(Year)-CurrentYear-1 = -1 for 2010 (and -2 for 2009, etc...)

      But this calculation does not work inside the year2date formula (it does outside of it).

      Any help on this would be highly appreciated. Many thanks in advance,

       

      Josetxo

       

       

       

        • Year2Date function
          Shivarama krishna K

          Hi,

          Use the Set anlaysis to achieve the required thing.

          ex

          Sum( <Year=$(#=Max(Year)>,Amount)

          and

          Sum( <Year=$(#=Max(Year)-1>,Amount) for Previous Month ,by default it points to the Max Year in the Date filed.

            • Year2Date function
              Josetxo Amonarriz

              Hello ajay,

              Thanks for your reply.

              The problem with your suggestion is that it sums all the sales for last year, not only those which are in the 1/1/2009..23/4/2009 range.

              I would need only the Year2Date vs. LastYear2Date sales (and LastYear2Date vs. LastYearDate -1 sales)

              Regards,

              Josetxo

               

                • Year2Date function
                  Josetxo Amonarriz

                  I mean: can I make the year2date offset a calculated expression, instead of a fixed value?

                   

                  Regards,

                  Josetxo

                    • Year2Date function

                      You could possibly set a variable called vYTD and call that through in the set analysis.

                      The variable definition can then use the Date/Time function InYearToDate.

                      Set Analysis is definitely the way to go ... I think if you get the InYearToDate function correct you may not even have to use a variable.

                        • Year2Date function
                          John Witherspoon

                          I think this:

                          sum({<Year={'$(=max(Year))'}>} Amount)
                          sum({<Year={'$(=max(Year)-1)'},Date={"<=$(=date(addmonths(max(Date),-12)))"}>} Amount)

                          But it depends somewhat on all the other fields on your calendar, and how you want things to behave based on selections in those other fields.

                            • Year2Date function
                              Josetxo Amonarriz

                              Many thanks for your answer.

                              But, it does show all the sales lines for a whole year instead of the sales lines until the date of today for that year, does it?

                              If I select 2007 in the filters, it sums all the sales for those 2 years. And I want only the sales until April 26 for both years.

                              So if I keep no filter, it confronts the sales for 2010 (until today) vs the sales for 2009...

                              I have tried setting the Year2Date formula offset this way:

                              =Sum(If(year2date(DateTransactionID,(max(Year)-CurrentYear)),SalesAmount))

                              but it shows nothing. So I tried this:

                              =Sum(If(year2date(DateTransactionID,(2007-CurrentYear)),SalesAmount))

                              and works... but of course I cannot keep the formula this way with a fixed value ;) Can I set up a variable in the offset?

                              Regards,

                              Josetxo

                               

                                • Year2Date function
                                  John Witherspoon

                                   


                                  SALTO wrote:But, it does show all the sales lines for a whole year instead of the sales lines until the date of today for that year, does it?


                                  No, that expression does NOT select the entire current and previous year. It did have a bug in the current year, but the result was that it only used the selected date. There was no bug in the previous year expression. It looks ONLY at the correct date range in the previous year. That's what the Date={"<= portion of it does. See the addmonths(...,-12)? It's subtracting 12 months from the date you selected, and only taking dates less than or equal to that in the previous year. But I needed to do the same thing in the CURRENT year. So this:

                                  sum({<Year={'$(=max(Year))'},Date={"<=$(=max(Date))"}>} Amount)
                                  sum({<Year={'$(=max(Year)-1)'},Date={"<=$(=date(addmonths(max(Date),-12)))"}>} Amount)

                                  See attached.

                                • Year2Date function

                                  Hi John,

                                  I am having a field and contains values init. i.e: MeasureLength(field) and its values(1,2,1,6,8,10,10, 12, 20,..........)

                                  My requirement is to get a count of MeasureLength between ranges

                                  i.e; MeasureLength >=1 and MeasureLength <10 then count should be 7

                                  MeasureLenght >=10 and MeasureLenght <20 then count should be 4, ........

                                  Can you help me to solve this problem

                                    • Year2Date function
                                      John Witherspoon

                                      Well, there are a lot of answers depending on what you're trying to do. Like if you're trying to create a histogram of the count in each range, and the ranges are all the same size, you might do something like this:

                                      Dimension = class(MeasureLength,10)
                                      Expression = count(MeasureLength)

                                      You could also do the class() function in the script for greater chart speed at the cost of making it a little harder to change, and not being able to do clever things like make a user-enterable class width.

                                      If your ranges are not all the same size, you could do similar with nested if():

                                      if(MeasureLength<10,dual('0-<10',0)
                                      ,if(MeasureLength<25,dual('10-<25',10)
                                      ,dual('25+',25)))

                                      And again, this could be in the chart or the script, but I'd go with the script. If there are a lot of these ranges, you might want to make a table with the range data and interval match it onto your main table instead of having a huge nested if().

                                      Or maybe you just want those two specific ranges as expressions. You could do that with set analysis:

                                      count({<MeasureLength*={">=1<10"}>} MeasureLength)
                                      count({<MeasureLength*={">=10<20"}>} MeasureLength)

                                      And there are probably other things you could mean as well, with yet other solutions.

                                        • Year2Date function

                                          Hai John,

                                          IF i am using below process i am getting more count i.e if i am ranging from

                                          count({<MeasureLength*={">=240<250"}>} MeasureLength) it is showing count=6 but actually the count is 4.

                                          How i solve this?

                                          Thnks

                                            • Year2Date function
                                              John Witherspoon

                                              It produces the counts I would expect on the data set you gave (which incidentally are not the counts you listed, which assume <= instead of <). See attached. Give me some example data where it fails and I can try to debug it, but it looks fine to me.

                                              One possible problem would be if MeasureLength is a key field linking two tables. Counts don't really work well in that case, and you might need to create a counter field in your main table. But MeasureLength seems unlikely to be a key field.

                                        • Re: Year2Date function

                                          Many thanks John !!