6 Replies Latest reply on Aug 17, 2016 2:45 AM by khalid norat

    If selected Month then Month if ALL then Latest Month

    lalitha p

      Hi Everyone,

       

        I have a requirement like this. I have a month filter and a report with sales across my customers is displaying. If i select Month = "Aug" then i should display Aug month customers with their sales and if July then July month Customers with their sales and if select "ALL" it should show the latest month of customers(i,e Aug month customers with their sales) If I don't have Aug month populated in my DB then for "ALL" my report should display July i,e clearly if Month  = "ALL" then it should be the latest month(Max Month) of customers with their sales should display.

       

      Note: Can i develop this kind of scenario with out Parameter. Because Parameter i should not use in my reports.

       

      Please help me up!!!!!!!!!!!

        • 1. Re: If selected Month then Month if ALL then Latest Month
          pulkit jain

          make your month filter as context filter

          create a calculated field countd(month)

          if countd(month) =1 that means user have selected a month.

          if countd(month)>1 that means user have selected 'ALL'

           

          you can create your further calculation logic over this

          • 2. Re: If selected Month then Month if ALL then Latest Month
            Andrew Watson

            Effectively you want to display the latest month - that would be the only month when a single month is selected or the latest when many months (ALL) are selected.

             

            I haven't tested this but if you put the Month filter to context as Pulkit suggests then you can create another calculated field which should only display the latest month:

             

            MONTH([DateField) = {FIXED:MAX(MONTH([DateField])}

             

            Drag that field to filters and set to True. I think the Month filter would have to be in context for this to work, with the above calculated field formula out of context.

             

            Note if your date field spans multiple years this formula would need changing to account for the year as well.

            • 3. Re: If selected Month then Month if ALL then Latest Month
              lalitha p

              Hi Pulkit,

               

                 I have taken the same calculation and i have done this. But somehow it didn't come to me.

               

                 Here is my calculation .

                 I am actually trying to show my count of customers. If they select "Aug" then count of customers across my region in Aug month has to display. If ALL then it should be Aug Month only. (Latest Month)

               

               

              1)  if countd(Month) > 1 then countd(Customer) else countd(customer) end

               

              2) I tried to take max(Month) like this:

               

              IF

               

              MONTH(TODAY()) = MAX({ FIXED [Operating Region Description] , [Financial Year] , [Financial Month] : MAX(MONTH([Financial Date])) } )

              or MONTH(TODAY()) -1 = MAX({ FIXED [Operating Region Description] , [Financial Year] , [Financial Month] : MAX(MONTH([Financial Date])) } )

              and  SUM([Count of Month])>1  then  COUNTD([Customer Name (Number)])

               

              ELSEIF

               

              ATTR([Month]) ="Jan" or attr([Month]) = "Feb" or ATTR([Month]) ="Mar" or attr([Month]) = "Apr"

              or ATTR([Month]) ="May" or attr([Month]) = "Jun"  or ATTR([Month]) ="Jul" or attr([Month]) = "Aug"

              or ATTR([Month]) ="Sep" or attr([Month]) = "Oct" or ATTR([Month]) ="Nov" or attr([Month]) = "Dec"

              THEN max({FIXED  [Operating Region Description], [Financial Year], [Month] : countd[Customer Number] } ) END

               

              Somehow my requirement is i am trying to operating region, financial year and Financial Month so i am restricting to that level.

               

              Elseif part i can actually handle in simple Else part but somehow it was not working when i took in else part so handling like above.

               

              Though i am trying to pick the max month it is somehow not taking the max month count(customers) when mu sum(count of month) > 1 condiition.

               

              If any idea please let me know. Because of this requirement my overall dashboards are getting affected

              • 4. Re: If selected Month then Month if ALL then Latest Month
                lalitha p

                Hello everyone,

                For my above requirement atleast can we sort out with the help of the parameter. if so then i will try to convince my business with the parameter.

                 

                Here is my requirement :

                 

                   If i select Month = "Aug" then i should display Aug month customers with their sales and if July then July month Customers with their sales and if select "ALL" it should show the latest month of customers(i,e Aug month customers with their sales) If I don't have Aug month populated in my DB then for "ALL" my report should display July i,e clearly if Month  = "ALL" then it should be the latest month(Max Month) of customers with their sales should display.

                • 5. Re: If selected Month then Month if ALL then Latest Month
                  Łukasz Majewski

                  Sounds like you simply need the solution I put forwards here: https://community.tableau.com/ideas/1679#comment-18503

                  right?

                  • 6. Re: If selected Month then Month if ALL then Latest Month
                    khalid norat

                    Hi Lalitha,

                     

                    I have previously used the same method that Pulkit explained for a similar task.

                     

                    This is definitely possible without parameters.

                     

                    If you can please share a sample workbook or recreate your scenario with superstore data I can help you achieve the required result.