set analysis variable construction

[drohm002]

Please see my data table below.  I want to create a variable that counts the number of 'ID's with days less than 90.  When I create the variable on my own, it gives me 6 as the answer, but really the answer should be 2 ID's.  I know I need to use the distinct function, but I just cant get the formula to work.  How can I accomplish this?  Thank you!

ID	days
1	5
1	5
1	5
2	20
2	20
2	20
3	100
3	100
3	100

[vishsaggi]

Can you share some sample data to work on?And you expected output you want to see. I mean which object? The screen shot you are sharing does not help coz that is in Excel. Right?

[eru-alelo]

Yes, I can send you the base of xls that contains the information that I am reading in qlikview.

I want to create scenarios by client, informing him at what stage of billing he is. or their behavior.

--------------------

Hello I'm Starting Without QlikView, And I Have A Base Of 'em In .xls I Need To Assemble A Billing Status Scenario In Some Variables.

.xls No 'I Can Get In This Condition, BUT No Qlik I'm Not Getting Cause It' s Necessary To Condition As Variables Month POR.

I added 2 Attachments, one of them and Image demonstrating hum Xls formula.

Another file in .xls, Where Do I CREATE A Non-Qlik Variable Taking Into Account As Column Z (Reference Data) INFORMATION + Billing Amount

[Faturamento]:

LOAD [Id Usuário Legado],

     [Proprietário: Nome completo],

     [Carteira: Nome de Carteira],

     CONSULTOR,

     [Superintendente: Nome completo],

     GERENTE_NACIONAL,

     GERENTE_REGIONAL,

     [Tipo de Mercado: Nome],

     [Tipo de venda: Nome],

     CONTRATO,

     [Produto: Nome do Produto],

     Safra,

     [Dados de Referência Safra],

     CNPJ,

     [Nome da Conta],

     Cidade,

     UF,

     [Grupo vendedor: Nome],

     [Agência bancária],

     [Descrição Canal de Entrada],

     [Vendedor PF],

     [Data Real Efetiva do 1º Pagamento],

     [Descrição Forma de Pagamento Contrato],

     [Qtd. Dias de Prazo de Pagamento],

     GRUPO_REL,

     DATA,

     DIA,

     MES,

     ANO,

     FATURAMENTO,

     [Rouba monte],

     Roubado,

     [Contrato roubado/roubou: Número do contrato],

     ID_GRUPO,

     [Data de Cadastramento],

     [Data do último faturamento],

     Status,

     Ativo,

     NOME_CARTEIRA as Nome_Cart

FROM

(qvd);

LEFT JOIN (Faturamento)

LOAD Distinct

ID_GRUPO,

(SUM (Faturamento) / COUNT (distintos (MES))) AS FAT_MÉDIO

residente Faturamento

Grupo POR ID_GRUPO;

LEFT JOIN (Faturamento)

LOAD Distinct

CONTRATO,

IF( MonthEnd( AddMonths(today(),-1)) -[Data do último faturamento] >90, 'CHURN 90',

IF( MonthEnd( AddMonths(today(),-1)) -[Data do último faturamento] >60, 'CHURN 60','Sem Faturamento'

)) as Batidão

Resident Faturamento

;

[vishsaggi]

Can you attach excel files can i use the same script to load from Excel. As your script has QVDs.

[eru-alelo]

I can not send the file in xls where it contains all the information that was uploaded to qvd.

[vishsaggi]

So where in the script you are having issues with? And how i can help you?

[eru-alelo]

Hello, I'm forwarding my base in xls, in the template I uploaded in qlikview

And in sheet 2 a summary with the scenarios that I want to create in qlik per client.

Column in yellow are my references for calculating the scenarios.

[vishsaggi]

Sure send it through. May be i would suggest you create a new thread and mention my name, i will pick it up.

[eru-alelo]

I created a question in the community with the following title

Create Billing Status Scenarios in Code / Criar Cenários de Status Faturamento no Código

[vishsaggi]

Create this as a new discussion, i cannot see this in the thread.

[eru-alelo]

I'M POSTING New in QlikView