2 Replies Latest reply: Feb 6, 2016 9:08 AM by Mohammad Adnan Ansari RSS

    Show totals based on selected dimension

    Mohammad Adnan Ansari

      I am working on Retail Sales Analysis.

       

      We have a Sales target let say 100 million (total) for the year. This is stored in a table named 'Target Overall' having attributes Year, Target.

       

      This target is divided into all stores we have except some new ones. So within 100 million 95mllion is distributed for the stores. 5 million is not yet allocated. This is stored in a table named 'Stores Target' having attributes Year, Store Id, Target.

       

      We have target specifically for some categories as well. This is defined overall for each category. Let say perfumes have a target of 20 million. Toys have a target of 5 million whereas edible items have a target of 8 million. Some of the target is still no yet allocated. This is stored in a table named 'Category Target' having attributes Year, Category ID, Target.

       

      Now the analysis is required to be designed in such a way that when we look at the overall Sales figures, it should compare it with the total target.

       

      When we drill down to stores (or look at store sales) , it should compare the sales with store targets.

       

      Similarly Category sales is required to be compared with category targets. But when we look at Sales for all categories; it should compare it with the total target of 100 million.

       

      How this can be done?

        • Re: Show totals based on selected dimension
          Gysbert Wassenaar

          Concatenate all you targets into one table and add a field with the level of the target, i.e. overall, store, category etc. It would be useful to use the same names as the field names of the dimensions like store and category. You can then create a chart with a drill down group with field names like store and category. You can then use the getcurrentfield function to retrieve the name of the current level of the drilldown group. That value can be used to select the right values from the targets table: sum({<Level={'$(=GetCurrentField([MyDrilldownGroup]))'}>}Target)

            • Re: Show totals based on selected dimension
              Mohammad Adnan Ansari

              It is good idea to do concatenating all the targets in one. There is one issue:

               

              I have 2 target tables here:

               

              first one is

              Store Day Target:

              -Date

              -Store id

              -Target

               

              second one is

              Category Month target:

              -Month

              -Category id

              -Store

              -Target

               

              If the store is able to reach its daily target; its OK. But in the long run like a Month's time, it has to achieve the targets on the categories as well.