topic Re: set analysis variable construction in QlikView

set analysis variable construction

drohm002 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 15:22:14 GMT

May be this?

= Count({<days = {"<90"} >}ID)

= Count(DISTINCT {<days = {"<90"} >}ID)

Re: set analysis variable construction

sunny_talwar — Thu, 09 Aug 2018 15:22:21 GMT

May be this

Count(DISTINCT {<ID = {"=Sum(days) < 90"}>} ID)

Re: set analysis variable construction

eduardo_dimperio — Thu, 09 Aug 2018 15:22:54 GMT

Maybe this

Count({<days={"=$(<=90)"}>}ID) in set analysis.

If you need in script i think is easier

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 17:53:35 GMT

Hello, would it be possible to add more than one variable in this formula?

Because I have a base that needs to do the same type of scenario, but with more variables

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 18:01:08 GMT

Can you give an example of what you are expecting?

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 18:02:13 GMT

= IF(and (Sum (month1, month2, month3) = 0; "Churn90"; if ((sum (month 1, month2) = 0; "Churn60"; "No Billing"

How could this variable be made?

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 18:05:38 GMT

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 18:06:50 GMT

Try this?

= IF( RangeSum(month1, month2, month3) = 0, "Churn90",

if( Rangesum(month1, month2) = 0, "Churn60", "No Billing"))

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 18:31:50 GMT

It did not work, because I'm trying to apply this variable to the script.

Where I need the form to add (Jan; Feb; Mar; Abr) = 0; Scene 1; and so on.

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 18:52:54 GMT

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?

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 19:12:35 GMT

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

;

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 19:34:27 GMT

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

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 19:36:38 GMT

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

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 19:52:37 GMT

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

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 20:14:22 GMT

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.

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 20:21:47 GMT

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

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 20:28:39 GMT

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

Re: set analysis variable construction

vishsaggi — Thu, 09 Aug 2018 20:40:44 GMT

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

Re: set analysis variable construction

eru-alelo — Thu, 09 Aug 2018 21:15:57 GMT

I'M POSTING New in QlikView