Set Analyses - Como usar IF no COUNT

[yurigomesdelima]

Boa tarde a Todos!

Eu preciso criar uma visualização onde eu exiba a quantidade de Funcionários Ativos por Centro de Custo e Data.

Exemplo:

	Janeiro	Fevereiro	Março
Faturamento	10	9	10
Tecnologia da Informação	5	6	7

No momento estou extraindo esses dados de um banco de dados onde possuo a tabela de Histórico de Funcionários que contém apenas os campos de Data de Admissão e Data de Rescisão para verificar se o funcionário está ativo ou não, ou seja, não possui um campo de situação/status.

FUNCIONAHIST:

MATRICULA	NOME	CPF	DATA_ADMISSAO	DATA_RESCISAO
1	JOAO	123.123.123.12	01/01/2019	05/10/2019
1	JOAO	123.123.123.12	01/01/2019
2	MARIA	456.456.456.45	03/04/2018	17/08/2019
2	MARIA	456.456.456.45	03/04/2018

A minha ideia para fazer isso era realizar um COUNT no CPF do funcionário SE esse funcionário possuir (Data de Admissão < Mês Escolhido AND Data de Rescisão = Nula) OR  (Data de Admissão < Mês Escolhido AND Data de Rescisão > Mês Escolhido).

Saberiam me explicar qual a melhor forma para eu desenvolver isso?

[diogoduarte]

Yuri, a melhor forma de você fazer certamente e criando um flag no script onde  você determina se o colaborador esta ativo ou não.

If(IsNull(DATA_RECISAO) = -1 , 1 , 0) as Colaborador_Ativo

Caso não seja familiarizado com a sintaxe de IsNull, segue o link com a explanação: https://help.qlik.com/pt-BR/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/NULLFunctions/...

E depois na expressão do count você faz o seguinte:

count({<Colaborador_Ativo={1}>} Distinct CPF)

Nesse caso o distinct não deve fazer diferença, mas eu utilizaria por precaução.

Dessa forma, o mês sendo uma dimensão ou um filtro deverá retornar o que eu entendi que você espera.

Não testei mas creio que você poderia fazer um set analysis vendo se o campo DATA_RECISAO={''} mas eu faria o flag no script.

[yurigomesdelima]

Olá, Diogo!

Sua orientação me ajudou em alguns aspectos e me ensinou algumas coisas, mas continuo com o problema, pois da forma que você demonstrou eu crio um estado definitivo para o funcionário e o que eu preciso é saber se em uma data específica ele estava ativo ou inativo.

Exemplo:

Com as informações abaixo preciso saber qual a Situação de JOAO nas datas 01/03/2019 e 01/07/2019.

MATRICULA	NOME	CPF	DATA_ADMISSAO	DATA_RESCISAO
1	JOAO	123.123.123.12	01/01/2019	05/10/2019
1	JOAO	123.123.123.12	01/01/2019	
2	MARIA	456.456.456.45	03/04/2018	17/08/2019
2	MARIA	456.456.456.45	03/04/2018

Da forma que fizemos ele ficará com a Situação Ativa independente da data.

Atenciosamente,

Yuri Aló

[yurigomesdelima · Accepted solution]

Diogo, consegui aqui!

Fiz o seguinte:

Count (
		{
        <FuncionarioDtAdmissao={"<=01-07-2019"},FuncionarioDtRescisao={">=01-07-2019"}> +
        <FuncionarioDtAdmissao={"<=01-07-2019"},SituacaoTipo-={'R'}>
		} 
Distinct FuncionarioCpf)

Agora só preciso conseguir gerar as datas automaticamente com os meses do ano atual, passar essas datas para variáveis e usá-las no set analysis, por que, da forma que fiz a data precisa ser inserida manualmente.

Muito obrigado pela ajuda! 😁

[diogoduarte]

Cara, não consegui responder antes.

E foi uma falta e atenção na minha resposta anterior. Mas se te ajudou em algo então ta bom.

Eu já fiz algo assim, mas não lembro se na época foi com set analysis.

Não to conseguindo um tempo pra fazer umas simulações aqui com set analysis, porque o Qlik executa o set analysis uma vez só na hora de executar o cálculo e não pra cada linha de registro, então se usar variável e tal ele vai calcular a variável uma vez e não pra cada mês da dimensão, me fiz entender?

Pra resolver sua situação, já que to sem tempo pra testar os set analysis e tal, da pra usar IF, veja o exemplo do script que alimentei com seus dados. (de brinde vai o calendário hehe)

//Calendário Geral
LET vDataInicial	= Num(Date('01/01/2018'));
LET vDataFinal		= Floor(YearEnd(Today(1)));

CALENDARIO:
Load
	DATA,
    Day(DATA)								as DIA,
    Month(DATA)								as MES_NUM,
    MonthName(DATA)							as MES_ANO,
    Year(DATA)								as ANO,
    If(DATA = $(vOntem) , 0 , 
    	If(DATA < $(vOntem) , -1 , 1))		as FLAG_TEMPO;
Load
	Date($(vDataInicial) + recno() - 1, 'DD/MM/YYYY') AS DATA
AutoGenerate $(vDataFinal) - $(vDataInicial) + 1;

Colaboradores:
Load *
InLine [
MATRICULA,	NOME,	CPF,	DATA_ADMISSAO,	DATA_RESCISAO
1,	JOAO,	123.123.123.12,	01/01/2019,	05/10/2019
1,	JOAO,	123.123.123.12,	01/01/2019, 31/12/2019
2,	MARIA,	456.456.456.45,	03/04/2018,	17/08/2019
2,	MARIA,	456.456.456.45,	03/04/2018, 31/12/2019
];

Aqueles que são ativos eu alimentei manual com o último dia do ano, mas ai você faz um tratamento pra alimentar com alguma data futura pois se estiver vazio não vai dar certo.

A medida de colaboradores ativos é a seguinte: 
Count(Distinct(If(MES_ANO >= MonthName(DATA_ADMISSAO) and MES_ANO < MonthName(DATA_RESCISAO) , CPF)))

E ai você pode transformar os MonthName em variáves se desejar. Creio que agora chega no resultado que você deseja.