8 Replies Latest reply: Mar 17, 2015 11:00 PM by Rakesh Mekapati RSS

    Bar Chart last 6 months trend

      hey guys....Probably a very simple problem but I've been stuck on this for quite a while.

      I have a guage chart object that displays the profit percentage for the selected month (list box object) and selected year (list box object).

      Underneath this, I wanted to display a bar chart that presents the profit percentage for the last 6 months from the selected month. For eg: If I select July 2009, guage chart displays profit percentage for July and the bar chart should display the profit percentage for the months - Jan, Feb, Mar....Jun.

      Any ideas please?

       

        • Bar Chart last 6 months trend
          John Witherspoon

          I'd handle it like this (untested):

          ,date(monthstart(Date),'MMM YY') as Month
          ,date(yearstart(Date),'YYYY') as Year

          sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Sales)
          / sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Cost) - 1

            • Bar Chart last 6 months trend

              John,

              This worked out perfectly. It is exactly what I was looking for.

              Thanks very much for your help!

               

                • Bar Chart last 6 months trend
                  beciafreestyle

                  Dear Quote,

                   

                  I would like to attach similar bar chart into my analysis, but because I am on a begginer level I have no idea where to use expression that John adviced. Could you describe me in details how it works?

                   

                  Thank you in advance,

                  Beata

                    • Bar Chart last 6 months trend
                      John Witherspoon

                       


                      beciafreestyle wrote:I would like to attach similar bar chart into my analysis, but because I am on a begginer level I have no idea where to use expression that John adviced. Could you describe me in details how it works?

                      Sure. The first two lines:

                      ,date(monthstart(Date),'MMM YY') as Month
                      ,date(yearstart(Date),'YYYY') as Year

                      Belong in the script. I would typically load these to a separate Calendar table, but you could also load them wherever you're loading your Date field. These define the Month and Year fields as the first day of the month, and the first day of the year. But they only display the month and year, and the year respectively. I like defining my Month and Year as actual dates, because it allows me to use all of the available date functions on them. And that's part of what we're about to do with this expression:

                      sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Sales)
                      / sum({<Date=,Year=,Month={">$(=date(addmonths(max(Month),-6),'MMM YY')) <=$(=date(max(Month),'MMM YY'))"}Cost) - 1

                      In this case, you would make a bar chart with Month as the dimension. Then you would enter this whole thing as the expression on the expression tab. Since both the first and second line of this are nearly identical, let's just talk about the first line.

                      The core of what we're doing on that line is sum(Sales). We want the sum of some sales. But since we've selected a specific month (according to the original post), a simple sum(Sales) would only return the sum of the sales for that specific month. Instead, we want the sum of sales for the past six months. One way to accomplish that is using set analysis. You can identify set anlysis by the curly brackets, {}. Everything within the curly brackets is our set analysis expression. By default, these expressions MODIFY the current set rather than starting over with a blank set. So we're starting with the set where the user has selected a specific Month. The "Date=,Year=," tells QlikView to ignore any selections in these two fields. Pretend nothing has been selected. While perhaps not strictly necessary, this is at least a safety measure since date fields are often selected in combination. You would want to list EVERY date field related to the date field you expect to be selected. In this case, we're assuming that the only related date fields are Date, Month and Year. Then we get to our Month expression. The curly brackets enclose the expression. The double quote indicates that we're building a search string. We're not giving it a list of values. Instead, we're giving it a search string, which will cause it to look for matching values. The general structure here is Month={">some month <=some other month"}. So we're trying to give it expressions for a date range that includes the last six months. Let's start with the second part since it's a little simpler. We want dates less than or equal to the selected month. You might think this would be <=Month. Alas, not quite so simple. First, I assume that perhaps no months are selected, or many months are selected. What to do then? I've decided to use max(Month), so the most recent of the selected months. If no Month is selected, this will be the most recent month available. Working our way out, we then say date(...,'MMM YY'). Set analysis basically does a text comparison instead of a numeric or date comparison. So we need to make sure that our date, max(Month), ends up in the same format as Month for comparison purposes. The date() expression tells it an explicit format to use so that our comparison will work. The only part that is different for the "greater than" portion of our search string is the addmonths() function. In this case, we're subtracting six months from the selected month. Then we do the same thing with Cost to get the cost for the past six months. Divide, subtract one, and you have a six month margin as desired.

                      It is quite possible that your problem will have a MUCH simpler solution than this, though. Let's say you want a bar chart for the six-month rolling average of the sales. That is trivial to do in QlikView. Create a new bar chart, use Month as your dimension, sum(Sales) as your expression, then look down at the bottom left of the Expressions tab. There's an Accumulation section. Set the expression to "Accumulate 6 Steps Back". Done.

                        • Bar Chart last 6 months trend
                          beciafreestyle

                          Dear John,

                           

                          Finally I understood set analysis! I really appreciate your help and I would like to thank you for such extensive answerSmile

                          I already managed to make MasterCalendar, so I didn't need to put Year and Month into script, but the rest was like black magic. Anyway I still have some problems though, for example- now I'm trying to attach mini chart into my table, and I would like it to show Sales for months:

                          sum ({<CalendarDate=,CalendarYear= {2009},CalendarMonth=>}InvoiceAmount) and it doesn't work. Can it be fault of Label? Or anything else?

                           

                          Thank you in advance,

                          Best greetings,

                          Beata

                    • Re: Bar Chart last 6 months trend

                      Hi John,

                       

                      Same scenario only the thing is my source column value is not date instead it is like below.

                       

                      Calendarmonthyear:

                      JAN 2014

                      FEB 2014

                      MAR 2014

                      .

                      .

                      .

                      DEC 2014

                       

                      how can i show every time last 6 months trend