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

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

    Apoorva Dhulehole

      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.