5 Replies Latest reply: Sep 23, 2015 7:49 AM by Kumar Pramod RSS

    Count difference when used months

    Kumar Pramod

      Hi all,

       

      I am using the set analysis sum(user_signed) to count the number of user_signed.

      I am displaying count according to months wise also, I am using the below load script for months:

       

      MonthName(date_created) as Month_Year,

       

      In line chart:

      Month_year as dimension.

      sum(user_signed) as measure.

       

      when i added up all the months in line chart it is not matching with the KPI value which has measure:sum(user_signed).

       

      can anyone help where i am going wrong.

       

      Thanks,

      Pramod

        • Re: Count difference when used months
          Sunny Talwar

          Would you be able to share a sample?

          • Re: Count difference when used months
            Sasidhar Parupudi

            What is your set analysis expression?

            could you please post a sample?

              • Re: Count difference when used months
                Sasidhar Parupudi

                Hi

                Could please post your expression? I dont have qs on my machine..

                 

                when I used your expressions with your data file, my counts match for the problem year and month 182

                  • Re: Count difference when used months
                    Kumar Pramod

                    I found out the problem, i am using date_created for month_year when i used date_signed for month_year the value is getting fine.

                    what i have to do for date mapped with all the 3 measures.?

                     

                    But in line chart i am using 3 different measures :

                    1. Sum({$<MonthsAgo={">=1<max(MonthsAgo)"}>} user_signed)

                    2. Sum({$<MonthsAgo={">=1<max(MonthsAgo)"}>} course_completed)

                    3. count({$<MonthsAgo={">=1<max(MonthsAgo)"}>} user_signed)

                     

                    Dimension as : Month_year

                     

                    LIB CONNECT TO 'MyDB';

                     

                     

                    LOAD `invitation_id`,

                        `client_id`,

                        `batch_meta_data_id`,

                         activated,

                        `date_activated`,

                         date((date_created),'DD-MM-YYYY') as Date,

                         date#(left(date_created,10),'YYYY-MM-DD') as date_created,

                         time#(mid(date_created,12,8),'hh:mm:ss') as time_created,

                         date(WeekStart(date_created), 'MMM DD') &' - '& date(WeekEnd(date_created), 'MMM DD')  as Week_Group,

                         week(date_created) as week_number,

                         //MonthName(date_created) as Month_Year,

                         weekstart(date_created) as weekstart,

                          Today() - date_created as DaysAgo,

                         // 12 *(Year(Today())-Year(date_created)) + Month(Today()) - Month(date_created) as MonthsAgo,

                         WeekDay(date_created) as WeekDay,

                         month(date_created) as Month_Number,

                         `campaign_name_id`;

                    SQL SELECT `invitation_id`,

                        `client_id`,

                        `batch_meta_data_id`,

                        activated,

                        `date_activated`,

                        `date_created`,

                        `campaign_name_id`

                    FROM rcdbrpt.invitation WHERE client_id in (6,12,13,15,16,3,8,10);

                     

                    LIB CONNECT TO 'MyDB';

                     

                     

                    LOAD `course_completion_id`,

                        `invitation_id`,

                        `final_test_status`,

                        `course_completed`,

                        date(floor(date_completed)) as date_completed;

                    SQL SELECT `course_completion_id`,

                        `invitation_id`,

                        `final_test_status`,

                        `course_completed`,

                        `date_completed`

                    FROM rcdbrpt.coursecompletion;

                     

                    LOAD `invitation_id`,

                        `user_signed`,

                        landing_page_accessed,

                         MonthName(date_signed) as Month_Year,

                         12 *(Year(Today())-Year(date_signed)) + Month(Today()) - Month(date_signed) as MonthsAgo,

                       date(floor(date_signed)) as date_signed;

                    SQL SELECT `invitation_id`,

                        `user_signed`,

                        `date_signed`,

                        landing_page_accessed

                    FROM rcdbrpt.signupactivitylog WHERE client_id in (6,12,13,15,16,3,8,10);