3 Replies Latest reply: Aug 13, 2015 3:20 AM by Jonathan Dienst RSS

    need only last 4 four weeks data

      Hi Experts,

       

      I am new to qlikview.

      Here is my requirement

      I nee to display last 4 weeks data from max(date) only and it should roll up week wise. Here date I am getting as time stamp.

      I know how to get week wise data. But problem is to get last 4 weeks data only.

      Can somebody help me on this issue.

       

      Regards,

      Nanada

        • Re: need only last 4 four weeks data
          Jonathan Dienst

          Create a table with the week as the dimension, and the following expression:

               =Sum({<datefield = {">=$(Date(Max(datefield) - 28)) <=$(=Date(Max(datefield)))"}>} metricfield)

           

          Replace datefield and metricfield with the correct fieldnames.

          • Re: need only last 4 four weeks data
            Rudolf Linder

            you can do it either in calculated dimension or in Expression using set Analysis

             

            calculated Dimension:

            if (week(yourdatefield)>=week(max(yourdatefield))-4 and week(max(yourdatefield), week(yourdatefield)

            expression

            sum({<yourdatefield={'>=$(=weekstart(week(max(yourdatefield))-4)<=$(=weekend(week(max(yourdatefield))'}>}

            Be Aware that weeks before week 4 will not goback to previous year week (eg 52)

             

            if you have a weekfield the expressions can be easier

              • Re: need only last 4 four weeks data
                Jonathan Dienst

                You can let Qlikview take care of some of that, so if a calculated dimension is preferred:

                 

                     Dimension: If(datefield >= Max(datefield) - 28 And datefield <= Max(datefield), week(datefield))

                     Expression: Sum(metricfield)

                 

                (But I think the set analysis on the metric in my first post will be faster if you are dealing with a large data set)