6 Replies Latest reply: Jun 5, 2015 4:38 PM by Ahmet Erispaha RSS

    Set Analysis Question in QlikSense - Period over Period

      Good Morning,

       

      As someone that is still rather new to this program understanding what I need to ask and knowing what the community and application call it has been a struggle and although Chris has been helpful using some other posts to better understand how to ask what I need some help with has prompted me to edit this post and re-ask the question. However I have kept the original question for reference.

       

      I am looking to create a set analysis grouping that will segment current year sales as well as my fiscal periods that make up my first sales quarter. I would like to create this as a new Measure so that I could create additional measures that would represent my second quarter, third and final forth quarter sales results.

       

      The set analysis formula that I am currently using to calculate "Current Year" is as follows,

       

      Sum({$<FISYR = {$(=year(Today()))}>} SASALES)

       

      I now need to modify the current formula to include additional filtering for the fiscal period grouping. The field that contains this information is named "FISPER" and my periods are identified as numeric values, for example Period number 1 is represented as "1" so to make up my current year sales quarter it would be a combination of the field FISPER = 1, 2, 3

       

      Thank you for your assistance.

       

       

      (Original Question)

       

      Good Morning Everyone,

       

      The powers that be have come to me with a question and I was not sure how to go about creating this so I thought I would ask... we are creating a customer scorecard of sorts that we can compare things like on time delivery and order fill rate, this is all pretty basic stuff however we do have some customer that evaluate us on a quarterly bases and much like a year over year visualization would be I am trying to build a period over period measure to build in to my visualization.

       

      This does not even have to be period it could be more generic like last 90 days over the previous 90 days...

       

      I am hoping someone could help me with this.

       

      This formula that I am using for the current fiscal year would I change this or do I need to use a new formula, something to identify is that I do have a PERIOD specific field that has 12 periods in it that field is called FISPER I am not sure that would make a difference but thought I would include.

       

      Sum({$<FISYR = {$(=year(Today()))}>} SASALES)

       

      Thanks

       

      Modified the question to present a better understanding of what I was asking. The original question was a little hard to understand Message was edited by: Barry Taylor

        • Re: Period over Period Measure Question
          christian juillard

          Hi Barry

           

          you can adapt this

           

          tell me if it fits your needs

          best regards

          Chris

            • Re: Period over Period Measure Question

              Christian,

               

              Thank you for your reply I am rather new to this and I might have posted in the wrong area, I am not using QlikView I am using QlikSense

               

              I hope your still able to assist

               

              Thank You

                • Re: Period over Period Measure Question
                  christian juillard

                  Hi Barry,

                   

                  i haven't tried Qlik Sense much yet.

                  Set analysis doesn't work with qlik Sense ?

                   

                  Chris

                    • Re: Period over Period Measure Question

                      Hey Chris,

                       

                      Thanks for getting back to me, sorry it took so long for me to get back to you I was attending some Salesforce.com events and was un able to reply.

                       

                      Yes Qlik Sense does manage set analysis as I have used it for the year over year feature how ever I am struggling with how to manage and group the period over period segments.

                       

                      Here is the "Current Year" set analysis code that I have been using.

                       

                      Sum({$<FISYR = {$(=year(Today()))}>} SASALES) I am looking to modify this to include sales from the current year and from an additional field called FISPER with a range of 1, 2, 3 which would equal the first quarter. and then a second measure that would include range 4, 5, 6 for Quarter 2 etc. the end result I am hoping to caputre is sales for a specific year and for a specific quarter that I can then layer as a side by side comparison in a bar chart or something

                • Re: Set Analysis Question in QlikSense - Period over Period

                  Good Afternoon,

                   

                  Well I have to say this is a really great program I spend the watch videos and learning some of the finer parts, I have figured out how to make the formula work.

                   

                  For anyone that might be having the same beginner challenges that I have here is a copy of the script.

                   

                  Thank you everyone for your posts and additional posts that I have found very useful

                   

                  Sum({$<FISYR = {$(=year(Today()))}, FISPER = {'01', '02', '03'}>} SASALES)

                  • Re: Set Analysis Question in QlikSense - Period over Period
                    Ahmet Erispaha

                    Hi Barry,

                     

                    Maybe the attached will give you some ideas.

                     

                    This kind of stuff works well if you create Boolean flags in the script.  Notice there is a flag called CYTDFlag which marks each date in the calendar with a 1 or 0 depending on if it's in the current year or not.  You could similarly mark a date as being in the current quarter, etc.  This example also generates a simple version of a master calendar with date parts like Year, Quarter...  There are lots of examples of this kind of stuff in the Community.  The table in the example has two expressions - one totaling transaction amounts for Q1 of the current year and the other totaling transaction amounts for Q2 of the current year.

                     

                     

                    Quick and dirty but hope it gets you started.

                     

                    Think about signing up for the Data Modeling for Qlik Sense course if you have not yet taken it.

                     

                    Good Luck!

                     

                    Ahmet