Count changing form month to month

[Anonymous]

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.

[Anonymous]

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

[Anil_Babu_Samineni]

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

[Anonymous]

Hi Anil,

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

regards,

Ram

[Anil_Babu_Samineni]

I would say "Primary Key" of Date field

[madhumitha]

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!

[luismadriz]

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

[Anonymous]

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

[Anonymous]

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

[pradosh_thakur]

try this

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