15 Replies Latest reply: Apr 12, 2011 7:22 AM by new_user3 RSS

    Can you check this expression please?

      Hello All,

      Unable to figure out if there is something wrong in this expression here:

       

      =sum({$<TIME_PERIOD_WEEK_NO = "Week(Today())-52">}WK_SALES_GROSS_REV)

      I am trying to calculate the rolling weekly sales amount. So I want to do a Current week - 51 weeks at any point of time on a chart. I already posted, but could not get any reply for this. I am sure someone from you might have done this before I am currently in week 9 of 2011 and I want to show the data for this 9 weeks till the week 10 of 2010.

      Please help.



        • Can you check this expression please?
          Miguel Angel Baeyens de Arce

          Hello,

          Something like the following might work

           

          sum({$<TIME_PERIOD_WEEK_NO = {'>=$(=Week(AddYears(Today(), -1)))'} >}WK_SALES_GROSS_REV)


          Anyway, it's always better to work with dates, since as a year has 52 weeks, you lck of any other field such a year to identify which dates you want to aggregate. So instead something like

           

          sum({$< DATE = {'>=$(=AddYears(Today(), -1))'} >} WK_SALES_GROSS_REV)


          having Week as dimension should work as well.

          Hope that helps.

            • Can you check this expression please?

              Hello Miguel,

              Thank you for the quick reply, I was playing around with the expressions as suggested by you. When I use the first expression as suggested by you, it shows the fixed 52 weeks data on the chart and doesnt change when I click on Year 2011. I was expecting to change it from week 10 2010 to week 9 2011 when I click on 2011. I also tried your second expression as suggested by using the Year as below, and this doesnt work at all.

              =sum({$< TIME_PERIOD_YEAR = {'>=$(=AddYears(Today(), -1))'}>} WK_SALES_GROSS_REV)

              Any further help please?

              Thanks in advance,



                • Can you check this expression please?
                  Miguel Angel Baeyens de Arce

                  Hi,

                  AddYears() doesn't return a year, but a date. So you have to use your Date field instead your Year field.

                  Regards.

                    • Can you check this expression please?

                      Hello Miguel,

                      I have the Dimension called TIME_PERIOD_START_DATE in which I have 12 periods and the dates in the format of YYYY-MM-DD hh:mm:ss and I tried to use it, can you check this and let me know whats wrong. The problem is I dont want to select any week, but the chart should show the fixed data for Current Week - 51 weeks at any point of time(rolling weeks).Please help me with this.

                       

                      =sum({< TIME_PERIOD_START_DATE = {'>=$(=TIME_PERIOD_START_DATE(Today(), -1))'}>} INV_NET_REV)/1000

                       

                       



                        • Can you check this expression please?
                          Miguel Angel Baeyens de Arce

                          Hello,

                          Check with something like the following:

                           

                          sum({< TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'}>} INV_NET_REV)/1000


                          TIME_PERIOD_START_DATE is not a function, so it doesn't accept parameters. I've used here AddYears, because you want the last 52 wweks (that is one year ago).

                          Hope this helps.

                            • Can you check this expression please?

                              Well Miguel,

                              The suggestion you made looks like working, but I am not working with full data so cannot validate that yet. However my question is, Its showing the last 52 weeks data but I want that from Today - last 52 weeks and when we do add years, will that still consider the current year 10 weeks and add the rest of the 42 weeks from previous year? As thats my requirement.

                              Thanks for your time and help.

                              Andy

                                • Can you check this expression please?
                                  Miguel Angel Baeyens de Arce

                                  Hello Andy,

                                  That expression will Sum everything from one year ago on. So for today, 03/03/2010, for tomorrow, 04/03/2010, the day after tomorrow, 05/03/2010, and so on. If this expression is in a chart where the dimension is Week, it will display the information per weeks, taking into account the current one 10-2011, from 11-2010 last year. The next week, it will display from 12-2010 to 11-2011, and so on.

                                  Hope that helps.

                                    • Can you check this expression please?

                                      Hello Miguel,

                                      I was trying this query I posted sometime back as I started working with the Previous year data now. When I used the above expression as mentioned by you, it doesnt seem to work? I have a Time dimension field for TIMEYearWeek which is in the format of 201101 and I have another dimension like TIME_PERIOD_WEEK_NO which is just a number from 1-52 for any year selected and I have a TIME_PERIOD_START_DATE which is like YYYY-MM-DD hh:mm:ss dimension whcih actually holds the data in Time stamp format for a period whenever started..

                                      Can you please help me build a chart which always shows rolling current - 52 weeks data?

                                      Thanks a Ton,

                                      ANDY

                                      • Can you check this expression please?

                                        Hello Miguel,

                                        It looks like its working, however please look at this expression below, the problem is I have the Forecast data for the whole year 2011 and the chart is obviously showing the line for the whole year after 52 weeks calculation, so was wondering how do i fix it only to show till the current week just like the sales data??

                                        =sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'},FORECAST_TYPE= {'Q0F'}>}FCT_GROSS_REVENUE)/1000

                                        Thanks a Ton,

                                        ANDY

                                          • Can you check this expression please?
                                            Miguel Angel Baeyens de Arce

                                            Hello Andy,

                                            This expression is always going to show the last 52 weeks until today, since you are actually using Today() function in your set analysis. So can you please upload some sample data or elaborate on what you want to get?

                                            Anyway, the date comparison in set analysis modifiers can be used as a range like the following

                                             

                                            =sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))<=$(=Date('01/01/2011'))'},FORECAST_TYPE= {'Q0F'}>} FCT_GROSS_REVENUE)/1000


                                            The example is silly, but may help you understand how that works a date greater than or equal to today one year ago until the first day of 2011.

                                            Hope that helps.

                                              • Can you check this expression please?

                                                Hello Miguel,

                                                Once again I appreciate your patience in responding to queries and helping people like me. The below is what I see when I use the above expression: The problem is the FORECAST Line shows for the whole year ignoring the Today() as the data is planned and available for the whole year before the begin of the year. I have the below expressions as seen below,

                                                http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4278.LikeThis1.JPG

                                                 

                                                Forecast =sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'},FORECAST_TYPE= {'Q0F'}>}FCT_GROSS_REVENUE)/1000

                                                 

                                                 



                                                 

                                                 



                                                Actual =sum({$<TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))'} >} WK_SALES_GROSS_REV)/1000

                                                 

                                                HOPE this helps as my QVW file is huge and cannot attach a sample for this to work...



                                                  • Can you check this expression please?
                                                    Miguel Angel Baeyens de Arce

                                                    Hello Andy,

                                                    Why don't you use the expression I showed above? Something like

                                                     

                                                    Sum({< TIME_PERIOD_START_DATE = {'>=$(=AddYears(Today(), -1))<=$(=Date(Today()))'}, FORECAST_TYPE = {'Q0F'} >} FCT_GROSS_REVENUE) / 1000


                                                    Although I'm using Today() to force exactly 52 weeks ago starting the count today, you can specify any other date, for example, the maximum date for "Actual" data, if it's not today.

                                                    Hope that helps.

                                                      • Can you check this expression please?

                                                        Hello Miguel,

                                                        I just wanted to let you know the actual requirement I had before trying anything, as I was buzy working on a huge Chart :-), anyways it worked and thanks for the help.

                                                        I have another question though, I have 2 hierarchies for customers, NAL2 and NAL3. I have more than 500 in NAL2 and 5000 in NAL3. The requirement is I need to build a Tracker for a Group of NAL2 and some under this for NAL3 in a Pivot. Currently I am doing this using the Valuelist and by a calculated Dimension. But its taking a lot of time as the chart has many calculated dimesions and If statements. Can you suggest a better way to do this? As in how can I do a Grouping of NAL2 and NAL3 customers and create a Dimension at the script level, so that All i have to do is use this Dimension and then write a simple expression to calclucalte the sales for a period...Hope you undestood the issue, The below is the sample set analysis query I am using for one customer, and I will send you more info if required.

                                                        if(ValueList('BOOTS')='BOOTS',sum({$< NPD_ID = {'NULL'},TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'},NAL2_ID = {'6170'}, NAL3_ID = {'9316'}, FORECAST_TYPE = {'CURRENT'} >}FCT_GROSS_REVENUE)+sum({$< TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'},NAL2_ID = {'6170'}, NAL3_ID = {'9316'}>}WK_SALES_GROSS_REV),

                                                        Like above I have a grouping of 45 customers and 4 different Pivots to show the details, I want to create this gouping at the script level and use the customer names as a Dimension to check if that increases the performance..

                                                        Thanks in Advance,

                                                        ANDY