Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Count difference when used months

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

Tags (1)
5 Replies

Re: Count difference when used months

Would you be able to share a sample?

sasiparupudi1
Honored Contributor III

Re: Count difference when used months

What is your set analysis expression?

could you please post a sample?

Not applicable

Re: Count difference when used months

HI sunny,

Please find the qvf and sample data.

For Ex:

In month jan 2015 i have 182 counts but in chart i am getting 141.

sasiparupudi1
Honored Contributor III

Re: Count difference when used months

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

Not applicable

Re: Count difference when used months

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);

Community Browser