5 Replies Latest reply on Feb 14, 2020 9:56 PM by Steve Wood

    filter by products with 100 sales by month

    Alex Martino

      Hello. I want to create a filter on a yearly timeline.


      However, I only want to include products that FOR ANY PARTICULAR MONTH (just one mont), surpassed 100 units in sales. If it surpassed it in one month, it will show data for all months for that product


      Category = product


      I tried this but it didn't work.


      {FIXED [Category],MONTH([Date/Time]): sum([Orders])}

        • 1. Re: filter by products with 100 sales by month
          Steve Wood

          Hi Alex,


          You could try changing your LOD calculation to something more like this:


          {FIXED [Category], YEAR([Order Date]):

              MAX( {FIXED [Category], DATETRUNC('month',[Order Date]): SUM([Quantity])} )





          In this case I've used quantity not orders, and a threshold of 300.


          Dragged onto Colour rather than filter to demo:



          The LOD says (working from inner to outer bit):


          - Get the SUM of quantity per category and month

          - Get the MAX of that

          - For each category and year*


          * I fixed at year because I filtered superstore data down to a single year.




          1 of 1 people found this helpful
          • 2. Re: filter by products with 100 sales by month
            Alex Martino

            Hi Steve, I think this is the right idea.


            A few questions/thoughts.


            I'd like to just not have the data points that are under 300 show up at all. So in your example, furniture would show all months but technology would not. I imagine I would just filter then?


            Also, you use filter at the year level. What do I do if I have multiple years?



            • 3. Re: filter by products with 100 sales by month
              Alex Martino

              Oh another question is why you use datetrunc and you can't use month(date/time) formula?

              • 4. Re: filter by products with 100 sales by month
                Bryce Larsen

                Hi Alex,

                You are correct in just using the function Steve Wood provided as a filter instead of on color.

                Reason to use DATETRUNC is that looks at individual months, ie February 2020. Just using MONTH() looks at all Februrary's in your dataset.

                No reason to filter on Year - I imagine he just did that to show you one year's worth of data - the above LOD he created would work regardless.

                The only caveat is, as a LOD expression, it would look at your entire dataset by default. If you filtered your view you would need to right click on the filter and select Add to Context.


                If the above helps, please let me know! But be sure to mark Steve's response as correct instead of mine as he provided the real solution.

                One comment is that it returns True/False per each Category and Year combination. If you want it only per category, you can remove the Year from the outer LOD expression.




                • 5. Re: filter by products with 100 sales by month
                  Steve Wood

                  Hi Alex,


                  Yep, as Bryce notes I dragged the calc onto the colours shelf just to demo (it's kind of hard to demo the absence of something so I figured colouring it would help show what was going on, sorry!). You would drag the calc onto filters and choose to only show the True values.


                  I'll echo Bryce's thoughts on why I used DATETRUNC. I'd love to say I planned it that way, but basically I tried it first with MONTH([Order date)] and ... it didn't work for the reasons Bryce explains (face palm), so I shifted to DATETRUNC. Same reason for including YEAR([Order date]) in the fixed dimensions in the outer LOD from recollection; it's just what I thought I needed to do for the view I had set up. As ever with fixed LOD calculations you just need to be aware of what dimensions you have in the view and what level of detail you want to jump up to.


                  I guess you could go with WINDOW_MAX(SUM([Quantity])) instead, set the computed along so that it's computing along month but partitioned by category (or whatever other dimension you want to show individual lines for), and then you could filter on that.


                  Anyway, let us know how you get on and if you need more input and someone will chip in I'm sure.