5 Replies Latest reply: Apr 29, 2015 6:40 AM by Matthias Elger RSS

    MTD for select year

    kumar avisakula

      Hi,

       

      I am new to qlikview and I have a requirement to do MTD for particular year.

       

      Here my expressions are

       

      Col1=   Count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"},[Site Visit Post Page Event] = {0}>} [Visitor ID])

      Col2=  Count({$<[Site Visit Hit Source]={"<>5,7,8,9"}, [Site Visit Post Page Event] = {0}>} [Visitor ID])

       

      But  I want to roll up monthly like below

       

       

      Pls help me how to resolve this issue.

       

      Regards,

      Kumar

        • Re: MTD for select year
          Matthias Elger

          SET TimeFormat='hh:mm:ss';

          SET DateFormat='DD.MM.YYYY';

          SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

           

           

          or

           

          You could load your data from sql with month & ' ' & year as month...

          • Re: MTD for select year

            Dear,

            For MTD  you will make in script year,month ,day of date.

            year(date) as year,

            month(date) as month,

            day(date) as day, 

            then   make a varible               =>   vCurrDate=today()-1

            Now use this code for MTD(month till Date).

             

            sum({<Year={$(=max(Year))}, month={$(=max(month))}, date={"<=$(vCurrDate)"}>}Col1)

             

             

             

            Regards

            Vimlesh

              • Re: MTD for select year
                kumar avisakula

                Hi,

                 

                In your expression

                 

                sum({<Year={$(=max(Year))}, month={$(=max(month))}, date={"<=$(vCurrDate)"}>}Col1) col1 is coming like

                 

                Count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"},[Site Visit Post Page Event] = {0}>} [Visitor ID])

                 

                So finally it will be

                 

                sum({<Year={$(=max(Year))}, month={$(=max(month))}, date={"<=$(vCurrDate)"}>}Count(DISTINCT{$<[Site Visit Hit Source]={"<>5,7,8,9"},[Site Visit Post Page Event] = {0}>} [Visitor ID]))

                 

                 

                The expression is validating perfectly. But the data is rolling up. It is giving blank.

                 

                Regards,

                Kumar