9 Replies Latest reply: Feb 28, 2016 8:43 PM by Shan Ahmed RSS

    Previous Year problem again

    Shan Ahmed

      Hi All,

       

      I have Month, Year and Sales field in an excel file. Month field contains values like 'January', 'February' in this

      format. Year field contains values like '2014', '2015', '2016' in this format. I would like to make the Month field

      values like 'Jan', 'Feb', 'Mar' this format. for this i used Capitalize(left(Month,3)) as [Sales Month].

       

       

      I Used Date#(Capitalize(left(Month,3)) & ' ' & [Year], MMM YYYY) as [Sales Month Year]. Then I created a variable

      vPreviousMonthYear(Date(addmonth(max([Sales Month Year]),-12), 'MMM YYYY') out of the [Sales Month Year] field.

       

       

      The purpose of creating the variable 'vPreviousMonthYear' is to use it in the calculation of

      1) Previous year sales, meaning that, if the user select 2016 from the year field then they can also see the 2015

      sales as the previous year sales, if they select 2015 that would be the current and 2014 would be the previous sales

       

       

      when the user select Year as 2016 and month as January the user will see the current year as 2016 and month as January

      sales But they also see the previous Year as 2015 and Month as January from Year 2015

       

      My calculation for previous year calculation is: sum({<Sales Month Year>={'$(vPreviousMonthYear'}'}, [Sales Year]=, [Sales Month]=>}Sales)

       

      One of my previous post with the same problem,  @Sunny T answered  the post and that was helpful but when i tried to

      resolve the real problem i have i am getting problem/issues.

       

      Can anyone help me solving the problem?


      Thanks & regards,

      ahmed100

        • Re: Previous Year problem again
          Settu Periyasamy

          Are you getting proper value for 'Sales month year ' value and vPreviousMonthYear value?

          If so, try like

          sum({<[Sales Month Year]={'$(vPreviousMonthYear)'}, [Sales Year]=, [Sales Month]=>}Sales)

          • Re: Previous Year problem again
            Sunny Talwar

            I guess my question is what is your dimension here? Is it Year dimension dimension? If it is then you won't be able to just use set analysis to get the result on the same line. For that you will have to use Above() or Below() function. I think if you can provide a sample which somewhat matches what your real scenario is, we might be able to help you better

            • Re: Previous Year problem again
              Shan Ahmed

              Hi Settu & Sunny,

               

              Thanks for your responses. I am sorry not to add a dimension to my example. I just added  the customer dimension to it. Please see the attach file.

               

              Settu, You are right! there is syntax errors in the calculation that i posted for the previous year calculation. But the original calculation i made in qlikview is same as you. So, i guess there is no issue on that. I am suspecting that that the way i created the PreviousMonthYear variable is not the right way to calculate it and thats why its not working in this particular situation i am trying to handle.

               

              vPreviousMonthYear = (Date(addmonth(max([Sales Month Year]),-12), 'MMM YYYY')


              Oh another thing, i was offline for quite a long time, thats why i couldn't reply post as soon as you guys reply my post.


              Please let me know if you have any question / if i am vague of putting forth information.


              Thanks & regards,

              ahmed100

                • Re: Previous Year problem again
                  Sunny Talwar

                  Are you hoping to see something like this?

                   

                  Expression for Previous Year Sales (PY Sales):

                  =If(Sum(Sales) > 0, Above(Sum({<Year, Month>}Sales), 12))

                  Capture.PNG

                    • Re: Previous Year problem again
                      Shan Ahmed

                      Hi Sunny,

                       

                      Thanks for the reply and the solution. The apps you attached works perfectly fine and its working as expected. But when i apply that in my real application it does not work for some reason. I just couldnt find the reason why it shouldnt work. Could you please give me some suggestions based on your experience where i am doing wrong what could possibly go wrong in this particular situation? Even though i totally understand that without looking at the real apps its quite impossible to say whats going wrong with the apps.

                       

                      Btw, Could you please explain what is this expression doing to calculate the Previous Year sales?

                      =If(Sum(Sales) > 0, Above(Sum({<Year, Month>}Sales), 12))


                      Thanks,

                      ahmed100

                        • Re: Previous Year problem again
                          Sunny Talwar

                          Another solution could be creating an As-of Table. Here is the script:

                           

                          Table:

                          LOAD Customer,

                            Month(Date#(Month, 'MMMM')) as Month,

                              Year,

                              Date(MonthStart(Date#(Month & ' ' & Year, 'MMMM YYYY')), 'MMM YYYY') as Date, 

                              Sales

                          FROM

                          [Data (5).xlsx]

                          (ooxml, embedded labels, table is Sales);

                           

                          Calendar:

                          LOAD Date as As_Of_Date,

                            Date,

                            'CY' as Flag

                          Resident Table;

                           

                          Concatenate(Calendar)

                          LOAD Date as As_Of_Date,

                            Date(AddYears(Date, -1)) as Date,

                            'PY' as Flag

                          Resident Table;

                           

                          Data model:

                           

                          Capture.PNG

                           

                          See if this solution is easier for you to implement. I can go over in details for the other if this doesn't work.

                            • Re: Previous Year problem again
                              Shan Ahmed

                              Hi Sunny,

                               

                              Thanks again for your reply. The attachment you posted works perfect but i cant make that work in my original application. I dont understand why its not working. Could you copy your email here? May be i could show you a fraction of the original data and you could tell me the issue. Please ignore this message if you dont share email.

                               

                              I appreciate your help.

                               

                              Thanks,

                              ahmed100