3 Replies Latest reply on Mar 21, 2016 12:44 AM by Sunil Rai

    Come up with a Comparative Filter Like for Like

    Sunil Rai

      Hi all,

       

      have been suffering with this for quite a while.

       

      I have a year date dimension. I have a fiscal week dimension as well.

       

      What I need to do is to ensure every store has sales for both year for the same fiscal week (since every year fiscal week repeats). So if a store has both sales data for both years, same fiscal week, then that is COMP. else it is not. I need it as a filter to filter my entire dashboard.

       

      Similarly, I will need for months as well but I suppose itll be the same concept.

       

      Please please help me here.

       

      Sunil

        • 1. Re: Come up with a Comparative Filter Like for Like
          Chandra Shekhar Jain

          Sunil

           

          Could you give the columns you are using in the viz.

          If you can send some data it will be even better.

           

          Chandra

          • 2. Re: Come up with a Comparative Filter Like for Like
            Mahfooj Khan

            Hi Sunil,

             

            You can create set to get this.

             

            e.g; create set by formula where you can put both year as well as fiscal year week conditions.

             

             

            //in formula

            SUM(IF YEAR([Order Date])=2012 THEN ABS([Sales]) END)>0

            AND

            SUM(IF YEAR([Order Date])=2013 THEN ABS([Sales]) END)>0

            The above set will return only those customers who have sale in both 2012 and 2013. Like that you can put your own conditions.

            I hope it may help you to get the idea.

             

            Mahfooj

            • 3. Re: Come up with a Comparative Filter Like for Like
              Sunil Rai

              Hey Mahfooj,

               

              something new I learnt today so thanks!

               

              I want to look at my stores and see which one has had sales for month Jan 2012 and month Jan 2013 and only if that store has data for that particular month across the 2 years, then its in my tabulation.

               

              The month always changes because I am looking at season to date so we keep on adding and I only want to compare with stores that last year also had the same time period full monthly data.

               

              So I was wondering if I can do up a calculated field that will always allow to change the calculation every month when I update my report monthly.