21 Replies Latest reply: Jul 24, 2017 4:27 AM by Apoorva SD RSS

    How to display data from Jan until previous month in QlikView?

    Apoorva SD

      Hello everyone,

       

      I have a table chart in which data has to be displayed from Jan of that year until previous month. What I mean by this is, I have an expression something like below:

       

      if(sum({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Actual Planned Hours) = 0, avg({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Actuals]),

      sum({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Act OEE]*[Actual Planned Hours])/sum({<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year={2017},Month={"Jan","Feb","Mar","Apr","May","Jun"},Date=>}[Actual Planned Hours]))

       

      the problem is, 1st of every month I manually enter previous month's name in the expression. Is there a way to automate this expression so that previous month is automatically picked up and the data displayed is sum until previous month?

       

      Any suggestion?

       

      Thank you.