5 Replies Latest reply on Aug 31, 2018 7:23 AM by Okechukwu Ossai

    Show this month and last month sale in column with a parameter selected

    Linda Dawson

      Hi,

       

      With Superstore sample data, I want to list this month sale and last month sale in the column for different region when I select a special date with parameter. How?

       

       

       

       

      Thanks,

       

      Linda

        • 1. Re: Show this month and last month sale in column with a parameter selected
          Okechukwu Ossai

          Hi Linda,

           

          There are different ways to do this. It depends on what you want to do with the results. I did separate calculations for month sales, so they can be used as inputs in other calculated fields if required.

           

          I assumed that Month Parameter are string calendar month names. See attached workbook.

           

           

          The solution has 2 options;  either calculate 'This Month' and 'Last Month' as Dimensions or as Measures.

           

          Create [Month Parameter]

           

          Create calculated field [Selected Month]

          IF DATETRUNC('month', [Date]) = DATEPARSE("dd/MMMM/yyyy",  "01" + "/" + [Month Parameter] + "/" + STR(YEAR(TODAY())))

          OR DATETRUNC('month', [Date]) = DATEADD('month', -1, DATEPARSE("dd/MMMM/yyyy",  "01" + "/" + [Month Parameter] + "/" + STR(YEAR(TODAY()))))

          THEN [Date] END

          The formula converts [Month Parameter] to a date and compares this parameter date to the database. It will return database dates equal to parameter date (this month) as well as dates equal to parameter date - 1 (last month). Add [Selected Month] to the filter shelf and exclude Null.

           

           

          Option 1: Display Results as Dimensions.

          Create calculated field [Sales Month]

          IF {FIXED : MAX(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN 'This Month Sale'

          ELSEIF {FIXED : MIN(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN 'Last Month Sale'

          END

          This returns string labels which can then be used later to display last month and this month sales.

           

          Option 2: Display Results as Measures.

          Create Calculated field [This Month Sale]

          IF {FIXED : MAX(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN [Sales] END

          This returns actual sale values for this month.

           

          Create Calculated field [Last Month Sale]

          IF {FIXED : MIN(DATETRUNC('month', [Selected Month]))} = DATETRUNC('month', [Date]) THEN [Sales] END

          This returns actual sale values for last month.

           

          Hope this helps.

          Ossai

           

           

           

          Hope this helps.

          Ossai

          4 of 4 people found this helpful
          • 2. Re: Show this month and last month sale in column with a parameter selected
            Michael Ye

            Linda,

             

            Follow these steps:

             

            (1) Create Custom Date with Order Date, select 'month' and date part.

            (2) Create parameter Based on this Custom Date. You will get month (1-12). This is a number value which permit you to do mathematical operation. For example, you select parameter=7 which means July, then [Order Date (Months) Parameter]-1 will be June, which is prior month.

            (3) Build two calculated fields:

             

            Selected month Sale:

             

            IF DATETRUNC('month',[Order Date])=DATETRUNC('month',DATE(STR([Order Date (Months) Parameter])+'/01/'+STR(Year([Order Date]))))

            THEN [Sales] END

             

            Prior Month Sale:

             

            IF DATETRUNC('month',[Order Date])=DATETRUNC('month',DATE(STR([Order Date (Months) Parameter]-1)+'/01/'+STR(Year([Order Date]))))

            THEN [Sales] END

             

            (4) Drop Dimensions and Measures to the View as following screen shot:

             

             

             

            Hope it helps.

             

            Michael Ye

            4 of 4 people found this helpful
            • 3. Re: Show this month and last month sale in column with a parameter selected
              Linda Dawson

              Okechukwu,

               

              Thanks for your reply. Your reply is very helpful. However, it looks not as simple as Michael Ye's solution, though both of your solutions work for me. Why do you use today() function? The month I selected in the parameter should have no relation with today's year, month or day.

               

              Linda

              1 of 1 people found this helpful
              • 4. Re: Show this month and last month sale in column with a parameter selected
                Linda Dawson

                Michael,

                 

                Your solution is very easy to understand, thanks!

                 

                Linda

                • 5. Re: Show this month and last month sale in column with a parameter selected
                  Okechukwu Ossai

                  You're welcome Linda. I used TODAY() because I assumed 'This Month' to be a month in the current year.

                  1 of 1 people found this helpful