4 Replies Latest reply: Apr 19, 2018 8:33 AM by Sunny Talwar RSS

    Set Analysis

    Ruma Barman

      Hi Team,

       

      I want to get the count of population of last day of the year for corresponding year in pivot table(App is attached).

      How to do this?

       

      Regards,

      Ruma

        • Re: Set Analysis
          Sunny Talwar

          Try this

           

          Count({<Date = {"=Date = YearStart(Date, 1)-1"}>}POPULATIONCNT)

          Capture.PNG

          • Re: Set Analysis
            Juan Pedro Barroso Rodríguez

            Hi:

            I prefer calculated this metrics in the script. I create a Flag that I use to count:

             

            //Your table

             

            Fact_t:

            LOAD

                "Count",

                POPULATIONCNT,

                "Date",

                Year(Date)             as Year,

                Month(Date)         as Month

            FROM [lib://Input/File.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

            // Select max Date

            MaxDay;

            LOAD

                MaxDate,

                1             as FlagMaxDate;

            LOAD

                Year,

                Month,

                Max("Date")    as MaxDate

            Resident Fact_t

            Group by Year, Month;

             

            //Add a Flag to count maxDate

             

            Fact:

            Load * Resident Fact_t;

            left join(Fact)

            Load * Resident MaxDay;

             

            Drop tables Fact_t, MaxDay;

             

            Best Regards,

            Juan P. Barroso

            • Re: Set Analysis
              Ruma Barman

              Hi Sunny,

               

              To get the count of previous year for corresponding year.How to achieve that?