2 Replies Latest reply on Dec 7, 2016 1:57 AM by Rochit Jain

    Conditional count using multiple data sources

    Rochit Jain

      Hello,

       

      I have two excel sheets in my data which I imported into Tableau -1. Sales and 2. Target  and it is at Store level.

       

      I have following variables -

      1. Sales TY - Sales for 2016 (in sales data)

      2. Target TY - Target for 2016 (in target data)

      3. Target Achievement - sum([Sales TY])/SUM([Target Data].[Target TY])

       

      Now in tableau in "Tgt Ach" tab I am displaying the Target Achievement by Store Code, so we can see that 9 Stores are above 100%.

       

      In "Store count" tab, I want to show the "total count of distinct Store codes" and "count of distinct store codes when Target Achievement > 100%". I am not able to show "count of store codes when Target Achievement > 100%". Can you please help. Your help is much needed and appreciated.

       

      I am using below formula but its showing 20 instead of 9

       

      IF [Target Achievement] >1

      then COUNTD([Store Code])

      END

       

       

      Thanks

      Rochit

        • 1. Re: Conditional count using multiple data sources
          Keshia Rose

          Hi Rochit,

           

          I've attached a workbook that has two examples on how you could solve this. The first (on Store Count,) is using your original data setup with blending and table calculations:

          Count of stores: WINDOW_SUM(COUNTD([Store Code]))

          Count of stores >100%: WINDOW_SUM([# Stores with >100% Tgt Ach])

          Once you have these calculations you must add store code to detail in order for them to work properly. This leaves you with duplications of the numbers so you can add a INDEX() = 1 calculation to only show the numbers once.

           

          However, if your data is similar to the sample I would suggest joining your data. It would make working with the data much easier and the calculations simpler since everything would just be in one data source. If you join you would be able to use LOD calculations and none of the index() = 1 stuff like I mentioned above.

          Target Achievement: { INCLUDE [Store Code] : sum([Sales TY])/SUM([Target TY])}

          I've included this example on the Store > 100 sheet

           

          Let me know what you think.

           

          Take care,

          Keshia

          • 2. Re: Conditional count using multiple data sources
            Rochit Jain

            Hi Keshia,

             

            Thank you so much for solving the problem.

             

            It worked perfectly! However: joining is not an option for me as the Sales and Target data are at different levels i.e. Sales is at day level and Target is at monthly level. Will have to work with blending and Index

             

            Have a great day!

             

            Regards

            Rochit