topic Re: Count changing form month to month in QlikView

Count changing form month to month

Anonymous — Tue, 17 Oct 2017 06:41:49 GMT

Hello All,

I am facing issue count of employees. I generated the report on 27-03-17 for the month of February data and the number is 16448 and I generated the report today for the month of February and the number increased to 16622. How can we make the number constant to 16448 forever. Looking forward for suggestions.

Regards,

Ram.

Re: Count changing form month to month

luismadriz — Tue, 17 Oct 2017 06:46:44 GMT

Hi Ram, your count shouldn't change for the same month but of course this depends on the calculation or if new data is being added for that period. Can you please share a bit more on the data or the calculation?

Regards,

Re: Count changing form month to month

Anil_Babu_Samineni — Tue, 17 Oct 2017 06:47:53 GMT

You can restrict the data using set analysis

Sum({<DateField = {'<= 27-03-17'}>} Measure)

Re: Count changing form month to month

its_anandrjs — Tue, 17 Oct 2017 06:49:13 GMT

May be you can use because data is get increases in the transaction table due to this. Better find the reason also with joins it happen or what.

=Count( DISTINCT employees)

Re: Count changing form month to month

Anonymous — Tue, 17 Oct 2017 06:51:33 GMT

Hello Luis,

Thanks for your prompt response. Below is the calculation formula

Count({$<_TransSubType={'CONTRACT','JOINERS'},%INSURANCE_TYPE_CODE={'1'},_YearMonth={'$(vCurrentMonth)'},Month=,Year=,[Month AR]=>} DISTINCT %COMPANY_NUMBER)

vCurrentMonth= =max(_YearMonth)

Regards,

Ram

Re: Count changing form month to month

Anonymous — Tue, 17 Oct 2017 07:33:32 GMT

Hello Anand,

I have replied with the calculation formula. Please check it.

regards,

Ram

Re: Count changing form month to month

Anonymous — Tue, 17 Oct 2017 07:34:44 GMT

Hi Anil,

Date is dynamic, User can select any day, but the count should be fix

Regards,

Ram

Re: Count changing form month to month

Anil_Babu_Samineni — Tue, 17 Oct 2017 07:40:16 GMT

Does this helps you?

Sum({<DateField = {"$(=GetFieldSelection(DateField))"}>} Measure)

Sum({<DateField = {$(Concat(Chr(39) & DateField & Chr(39), ','))}>} Measure)

Re: Count changing form month to month

Anonymous — Tue, 17 Oct 2017 07:55:00 GMT

Hello Anil,

calculation formula is: Count({$<_TransSubType={'CONTRACT','JOINERS'},%INSURANCE_TYPE_CODE={'1'},_YearMonth={'$(vCurrentMonth)'},Month=,Year=,[Month AR]=>} DISTINCT %COMPANY_NUMBER)

vCurrentMonth= =max(_YearMonth)

Suggest me what changes required on this formula to make count constant.

Ragards,

Ram

Re: Count changing form month to month

Anil_Babu_Samineni — Tue, 17 Oct 2017 08:05:37 GMT

That means, You want to count till 27-03-17 ??

Re: Count changing form month to month

Anonymous — Tue, 17 Oct 2017 08:20:32 GMT

Hi Anil,

not at all.. I am selecting date it can be any date...

ex: I selected today's date and the count is x. after 10 years I selected todays date means Oct- 2017. the data should be X.

Regards,

Ram

Re: Count changing form month to month

Anil_Babu_Samineni — Tue, 17 Oct 2017 08:27:02 GMT

How about this?

Count({$<_TransSubType={'CONTRACT','JOINERS'},%INSURANCE_TYPE_CODE={'1'},_YearMonth={'$(vCurrentMonth)'},Month=,Year=,[Month AR]=, DateField = {"=Only({1} DateField)"}>} DISTINCT %COMPANY_NUMBER)

Note - If none of them not working, I would ask you to share sample. Please share sample

Re: Count changing form month to month

Anonymous — Tue, 17 Oct 2017 10:16:18 GMT

Hi Anil,

Suggest me which date field I have to pass , and for sample the code is very huge

regards,

Ram

Re: Count changing form month to month

Anil_Babu_Samineni — Tue, 17 Oct 2017 10:17:57 GMT

I would say "Primary Key" of Date field

Re: Count changing form month to month

madhumitha — Tue, 17 Oct 2017 11:27:36 GMT

Hello Ram,

Are you using _YearMonth as your filter to select the date?

While you are restricting the _Yearmonth column to single date, you are allowing qlikview to consider all the months and Years ("Month=,Year="). this may be a reason.

Please attach a sample QVW for your issue.

Thanks!

Re: Count changing form month to month

luismadriz — Tue, 17 Oct 2017 22:57:14 GMT

Hi Ram, I would agree with Madhu that the use of YearMonth could be the culprit here and it's difficult to say without deeper understanding of your data and data model.

Kind regards,

Luis

Re: Count changing form month to month

Anonymous — Wed, 18 Oct 2017 05:16:25 GMT

Hello Madhu/ Luis,

Thanks for your reply and analysis. I do not find the attach option to attach the files. Please test me on rama79.putti@yahoo.com, I will send you the sample qvw/code

regards,

Ram

Re: Count changing form month to month

Anonymous — Thu, 19 Oct 2017 04:24:16 GMT

Hello Madhu,

Do I Need to change anything in my calculation condition. Below is the some code and calculation condition

CONTRACT:
LOAD INSURANCE_NUMBER & '_' & COMPANY_NUMBER & '_' & RG_BRANCH_CODE & '_' & REGISTRATION_START_DATE AS %BI_RG_CONTRACT_KEY,
INSURANCE_NUMBER,
COMPANY_NUMBER,
RG_BRANCH_CODE,
HIRE_DATE as [Hire Date],
If(Len(Num(Month(REGISTRATION_START_DATE)))=1,(Year(REGISTRATION_START_DATE) & '0' & Num(Month(REGISTRATION_START_DATE))),Year(REGISTRATION_START_DATE) & Num(Month(REGISTRATION_START_DATE))) as _RegStartYearMonth,
If(Len(Num(Month(REGISTRATION_END_DATE)))=1,(Year(REGISTRATION_END_DATE) & '0' & Num(Month(REGISTRATION_END_DATE))),Year(REGISTRATION_END_DATE) & Num(Month(REGISTRATION_END_DATE))) as _RegEndYearMonth,
If(Len(Num(Month(REGISTRATION_CREATE_DATE)))=1,(Year(REGISTRATION_CREATE_DATE) & '0' & Num(Month(REGISTRATION_CREATE_DATE))),Year(REGISTRATION_CREATE_DATE) & Num(Month(REGISTRATION_CREATE_DATE))) as _RegStartYearMonthSystem,
If(Len(Num(Month(RESIGNATION_CREATE_DATE)))=1,(Year(RESIGNATION_CREATE_DATE) & '0' & Num(Month(RESIGNATION_CREATE_DATE))),Year(RESIGNATION_CREATE_DATE) & Num(Month(RESIGNATION_CREATE_DATE))) as _RegEndYearMonthSystem,
Floor(Num(REGISTRATION_START_DATE)) as _RegistrationStartDateNo,
Floor(Num(If(IsNull(REGISTRATION_END_DATE),Today(),REGISTRATION_END_DATE))) as _RegistrationEndDateNo,
Floor(Num(REGISTRATION_CREATE_DATE)) as _RegistrationCreateDateNo,
Floor(Num(If(IsNull(RESIGNATION_CREATE_DATE),Today(),RESIGNATION_CREATE_DATE))) as _ResignationCreateDateNo,
RESIGNATION_CODE,
RESIGNATION_BRANCH_CODE

FROM
[$(vQVDsFolder)BI_RG_CONTRACT.qvd]
(qvd);
Left Join (CONTRACT)
LOAD Distinct %BI_RG_CONTRACT_KEY,1 AS INCLUDED_CONTRACT
Resident LINKTABLE
Where _TransSubType='CONTRACT';

Concatenate(LINKTABLE)// to add the records from contract table that excluded from the link table such as the records that has reg end date < reg start date etc..
LOAD Distinct %BI_RG_CONTRACT_KEY,
COMPANY_NUMBER AS %COMPANY_NUMBER,
INSURANCE_NUMBER AS %INSURANCE_NUMBER,
If(Left(COMPANY_NUMBER,4)='1011',1,If(Match(Left(COMPANY_NUMBER,4),'1064','1124','1074','1054','1044','1084'),4,If(mid(COMPANY_NUMBER,4,1)='2',2,If(mid(COMPANY_NUMBER,4,1)='5',5,If(Left(COMPANY_NUMBER,4)='1013',3))))) as %INSURANCE_TYPE_CODE,
RG_BRANCH_CODE AS %RG_BRANCH_CODE,
RESIGNATION_CODE AS %RESIGNATION_CODE,
RESIGNATION_BRANCH_CODE AS %RESIGNATION_BRANCH_CODE,
'CONTRACT' AS _TransType,
'CONTRACT' AS _TransSubType
Resident CONTRACT
where IsNull(INCLUDED_CONTRACT);

DROP Fields RG_BRANCH_CODE,RESIGNATION_CODE,RESIGNATION_BRANCH_CODE,INCLUDED_CONTRACT From CONTRACT;

calculation formula:

Count({$<_TransSubType={'CONTRACT','JOINERS'},%INSURANCE_TYPE_CODE={'1'},_YearMonth={'$(vCurrentMonth)'},Month=,Year=,[Month AR]=>} DISTINCT %COMPANY_NUMBER)

vCurrentMonth= =max(_YearMonth)

Regards,

Ram

Re: Count changing form month to month

pradosh_thakur — Thu, 19 Oct 2017 09:06:20 GMT

try this

Count({$<_TransSubType={'CONTRACT','JOINERS'},%INSURANCE_TYPE_CODE={'1'},_YearMonth={'$(vCurrentMonth)'},Month=p(Month),Year=p(Year),[Month AR]=>} DISTINCT %COMPANY_NUMBER)