9 Replies Latest reply: Jan 10, 2017 12:02 PM by John Witherspoon RSS

    Month on month sales in Line chart?

      Hi all,


      I'm sure this is a simple function but I can't seem to figure it out.


      In a line chart I want the total sales of the current year, and the previous year shown. However I want it shown not month by month but rather month on top of month. So let's say January sales where 10k, and February 15k, I want the point for February to express 25k, and when I reach December I want all of the months leading up to it's total shown ( let's say a total of 125k in sales). And then the same for the previous year.


      Does this make any sense to anyone here, and if so, how do I accomplish this?



        • Re: Month on month sales in Line chart?
          John Witherspoon

          If I've understood, a simple approach is Year and Month as dimensions, full accumulation on the expressions tab. A weakness is that if you select a month, you'll see that you no longer see an accumulated value, only the value for that month. Or if you select January, March, and July, you'll only see the accumulation of those months, with all other data ignored.


          A complicated approach is to use The As-Of Table. If you select a single month or a few odd months in that chart, you'll still see the accumulated values associated with those months. They will remain aware of all the other months involved in the accumulation even though you didn't select them. That's I think typically the behavior someone would want. A weakness is that it's complicated, and the two approaches don't play well together, so you have to be careful what you allow your users to select.


          See attached for both approaches.



            • Re: Month on month sales in Line chart?

              Hi John!


              The chart you're showing is exactly what I want, however I do not get it to work in my application.


              If I haven't selected a year, the chart show all years I have data for (five years), if I select a year (let's say 2015), it only shows that year. I would like for it to show the latest year and the one previous to the latest, in this case 2016 and 2015. I opted to use the first example and not the As-Of Table as I'm a bit strapped for time.


              Thank you

                • Re: Month on month sales in Line chart?
                  John Witherspoon

                  OK, to see something other than what you selected, in this case 2015 when you've excluded that from your selections, you need to use set analysis. The exact expression will depend on your exact requirement, how you've defined your year field, what other fields you have, whether you want to pay attention to them or not, or just some of them like ignore all calendar fields but pay attention to customer, say.


                  In the attached, we always show two years in the chart. If a year is selected, it's that year and the previous year. Else it's the two most recent years. If you select a month, it will compare that month for the two years, but only that month's sales, not the accumulation through that month. To see the accumulation through that month you'd need to select all months up to it. If you select a date, this will override and you'll only see that specific date. If you select any other fields, like ID, that will override as well, and you'll only see matching data. I don't know if this is the behavior you want.


                  Here's the expression I used.


                  sum({<Year={$(=max(Year)),$(=max(Year)-1)}>} Sales)

                    • Re: Month on month sales in Line chart?

                      Hi again John!


                      That was exactly what I was looking for, thank you very much!


                      I have a follow up question though, can I manipulate the chart so that it always shows the current and previous year only, despite other selections in the app?

                        • Re: Month on month sales in Line chart?
                          John Witherspoon

                          This should do the trick in the example. In your real application, list all your calendar fields where I have Date and Month, assuming you want to ignore all date-related selections and always show all of last year and the current YTD. You're telling QlikView to ignore selections in the listed fields.


                          sum({<Year={$(=year(today())),$(=year(today())-1)},Date,Month>} Sales)

                            • Re: Month on month sales in Line chart?

                              Hi, John!


                              I tried some stuff before reading your reply and I think this gives me what I (kind of) want, the current and last years sales. But perhaps it doesn't do YTD, and yours does?


                              sum({<Year={$(=max(Year)),$(=max(Year)-1)}>} Sales)


                              Also, the "today" part of your expression is a variable you've set or just something that is in your script?


                              Hope I'm making sense

                                • Re: Month on month sales in Line chart?
                                  John Witherspoon

                                  Today() is a function. It returns the current date. There's also now(), which returns the current timestamp. There are parameters you can set for these functions for different shades of meaning, but I rarely need to set parameters for them.


                                  Assuming you don't have data for future dates, then either version should give you year to date in the absence of selections, and the second will give you YTD even if some other year is selected.

                                    • Re: Month on month sales in Line chart?

                                      Hi again John,


                                      thanks for clearing that up.


                                      I have a question somewhat similar to what we've talked about here, and that is:


                                      how do I make a chart "untouched" by selections I make in the application?


                                      I would like the chart I have to be constant. (sales of all years)

                                        • Re: Month on month sales in Line chart?
                                          John Witherspoon

                                          Untouched by all selections is also set analysis, specifically the {1} set. So...


                                          sum({1} Sales)


                                          You can also modify the set with additional parameters. Like if you only wanted Sales of Apples, you could do something like this:


                                          sum({1<Product={'Apples'}>} Sales)


                                          I recommend doing some reading on set analysis. I think the help text is actually a pretty good basic introduction. I'm sure there are much more thorough treatments out there. It's a tool you very much want to have in your tool box. You'll love it once you get the hang of it. The only danger I've seen is a tendency to overuse set analysis because it's so... OK, maybe "easy" is the wrong word, because the syntax gets very complicated very quickly, but it's a tool that sometimes feels like it will do anything, so you'll use it over and over, and it isn't always the best solution, even if it's a solution. I prefer data model changes where practical, following my preference that complexity be in my script rather than in my charts. But I do use set analysis all the time still. And some people may prefer a simple script and complicated charts to a complicated script and simple charts.