3 Replies Latest reply on Sep 8, 2016 5:36 AM by Andrew Watson

    Previous Month in Map

    Scott Stinson

      Hi Gurus,

       

      I have a symbol map displaying profit from the standard Global Sales workbook. I want to display just a single month for order date and I have created a parameter for this. I also want a calculated measure to compare this month with the previous and measure improvement or deterioration and this is where I hat a problem.

       

      Using Lookup(Sum[Profit]),Last() -1) fails to return data. I think this is because I have only one month selected and so the previous is out of scope.

      If I change my date to run multiple months but do not add date to the detail it sums all months gives a incorrect result. What's more it still will not return a value in my formula.

      If I add order date to my detail I get multiple observations for profit and none for Lookup(Sum[Profit]),-1). Even if I change profit to be a calculated measure and get the latest lookup(sum([Profit]),last()-0) it still gives an observation for each month in my selection.

       

      I am a novice with this product and at this point it appears the entire lookup function does not work. Can someone provide guidance?

       

      Thanks

      Scott

        • 1. Re: Previous Month in Map
          Andrew Watson

          You're correct that you have filtered out the previous month so it returns nothing for a MoM. There are a number of ways around this and the best depends on your specific situation.

           

          You can try using a parameter and creating a calculated field to act as a filter based off that parameter selection. Parameters aren't dynamic so all values need to be entered into the parameter upon creation.

           

          Another way could to use a table calculation as your month filter. Create a calculated field along the lines of LOOKUP(ATTR[DATE],0) and that would be your filter rather than the date field.

           

          Even simpler is to just hide the previous month - however that would mean the report is quite static as you'll have hidden your chosen month to hide.

           

          SImplest way to get an answer would be to attach a tableau packaged workbook so someone can look at this instead of me throwing ideas at you to go and further investigate.

          • 2. Re: Previous Month in Map
            Scott Stinson

            Hi Andrew,

             

            I attached the workbook hoping that someone would help. I've spent a lot of time trying different solutions. The closest I came was to create a parameter to select just two months and then use calculated measures to restrict to the latest of the two. This works for sales and target sales. However I then want a score based on these two and a movement that compares this months score with the previous. There are table calculations that do this but they work at the level of individual rows and so are summed afterwards. I need to be able to sum the base measures and then create scores based on the sums.

             

            Any help would be appreciated.

             

            Scott

            • 3. Re: Previous Month in Map
              Andrew Watson

              I don't have tableau handy at the moment to open the workbook - however my reply to your message will bump your thread to the top and hopefully someone else will pick it up.