3 Replies Latest reply: Dec 6, 2017 8:08 AM by Supriya R RSS

    Apply measures for dynamic Dimensions in table

    Supriya R
    Supriya R Dec 4, 2017 1:08 AM

      Hi,

       

      i have a requirement where i need to create a table and for dynamic dimension(in my case its days ) i need to show count values for that particular dimension.

       

      Days               opens     clicks

      28/10/2017        3             3

      29/10/2017        1             0

      30/10/2017        13           10

      01/12/2017        2              1

      02/12/2017        4              3

      03/12/2017        6              5


      This is the table showing stats till today, tommorrow it will be 04/12/2017, day after tommorow it will be 05/12/2017 like this dimensions gets populated dynamically. So for this dimensions i need to show opens and clicks counts autometically. How could i do these?


      Please help me on this. Any kind of idea is appreciated.


        • Re: Apply measures for dynamic Dimensions in table
          Petter Skjolden
          Petter Skjolden Dec 4, 2017 1:59 AM (in response to Supriya R)

          In a load script this would probably be what you should make:

           

          OpensClicksPerDay:

          LOAD

               Days,

               Sum(opens),

               Sum(clicks)

          FROM

               Webstats.log (txt)

          GROUP BY

              Days;

          • Re: Apply measures for dynamic Dimensions in table
            Petter Skjolden
            Petter Skjolden Dec 4, 2017 2:06 AM (in response to Supriya R)

            You could also fetch in the data as-is without aggregating the data in a load script and then do a simple aggregation with sums in a visualization table in the app:

             

            LOAD

               Date( Floor(Days) ) AS Days,

               origin_IP,

               browser,

               opens,

               clicks

            FROM

               Webstats.log (txt);

             

            In a table in a sheet you would:

             

            Add Days as dimension and Sum(opens) in a measure and then Sum(clicks) in a measure.

              • Re: Apply measures for dynamic Dimensions in table
                Supriya R
                Supriya R Dec 6, 2017 8:08 AM (in response to Petter Skjolden )

                Hi petter-s

                 

                 

                yes your approch works for me only if sent,delievred,opens,clicks, bounce, unsubscribe fields are all in one table.

                 

                 

                What if i have 3 tables with fields and differnt datecreated

                Table1:

                invitation_id,

                client_id,

                batch_id,

                campaign_id,

                language_id,

                sent,

                date_created

                from table1:

                 

                 

                Table2:

                invitation_id,

                track_type,

                date_created

                from table2:

                 

                 

                Table3:

                invitation_id,

                bounce,

                unsubscribe,

                date_created

                from table3:

                 

                 

                Table4:

                id,

                track_type

                from table4;// track_type=1 is opened,track_type=2 is clicked

                 

                 

                here for each date i want to show counts  by campaign_id, batch_id, date_created,language_id;

                 

                 

                How could i do this?