
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The never answered question!
Dears,
I'm trying to create a personal Dashboard to study some Stock Market companies. It's a personal project, and I know nothing about this amazing software called QlikView. Most of you guys are helping me a lot, and I'm learning very much during this Corona's Crisis, once this is the only thing I can do during the crisis.
I was trying to summarize my questions, but I was not getting the answer I was looking for, so at this time I'll try to be more clear than ever.
So, let's get started:
1. I'm collecting the Stock Market prices from Yahoo.com, and I have the prices (VDAA) on a working day basis.
2. I'm collecting the company's Balance Sheet on Excel ".xls" format from my website, and I have the company's results on a quarterly basis. So, I have just 4 results per year.
Ir order to organize all my data, you guys helped me to create a calendar with days and quarter, as you can see below:
LET vStartDate = num(Floor(YearStart(Today(),-4)));
LET vEndDate = num(Floor(Today()));
LET vToday = num(Today());
LET vYesterday = num(Today()-1);
Calendar:
LOAD
Date#(date($(vStartDate) + RowNo() - 1), 'DD/MM/YYYY') as PERI,
QuarterName(Date#(date($(vStartDate) + RowNo() - 1), 'DD/MM/YYYY')) as QUAR
AUTOGENERATE
$(vEndDate) - $(vStartDate) + 1;
So, at this point, I have a calendar with all dates, and I can show on a chart the values of Dates (PERI), Stock Price (VDAA), and Company's Revenue (LOPP).
Take a look at 01/01/2020, the stock market is closed, but the companies have to send their Balance Sheet, so I have it, and QlikView is showing R$ 0,00 for VDAA (Stock Price). It's not correct, so this is my first question. How can I show the last available value when QlikView is showing R$ 0,00, like on 01/01/2020? I was expecting to see R$ 57,60
Ok, my second question is:
Most of the considerations I have to take in order to understand if it's a good or bad Stock to buy consider last year's result (12 months period). So, no matter what day is shown on PERI as a dimension, I always want to see the sum of LOPP of the last 4 quarters. And this is my second question. How can I show the sum of the last 4 available LOPP considering the day on the Dimension? Please, take a look at the example below! Of course, I simplified the date on the following example, but you can find my real project attached below.
And last but not least, comes an uncomfortable situation that is... As I mentioned above, the companies have to demonstrate their Balance Sheet on a quarterly basis, but they don't necessarily have to show it on the very first day of the next quarter, so, the results from the first quarter 2020 (represented by 31/03/2020) will not be available on 31/03/2020, it will be available sometimes 2 months later... So, the system should always consider the last 4 LOPP available. That's the reason why I can't work with 365 days period, or 1 year period, or something like that... How can I do it?
Please, check the example below:
How can I answer all of those questions guys?
With that, I'll be able to move on to my project!
I really appreciate all your help and assistance. You guys have a really good knowledge community!
Congratulations, and I'm sorry to ask lots of questions all the time.
It's not a simple universe for someone with no background in IT or programming!
Once again, thank you!
You can find attached my project!
*Note, sometimes you may get an error trying to load my data. It's due to a server's instability, try again and it will load!
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The first question should be fairly easy, assuming there's always exactly one VDAA value per date (otherwise, this approach will still work but you'll have to use an if() statement instead of the cleaner alt()):
=alt(only(VDAA),below(only(VDAA)))
I don't have a solution for your second question, unfortunately, but hopefully the first one will help.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The first question should be fairly easy, assuming there's always exactly one VDAA value per date (otherwise, this approach will still work but you'll have to use an if() statement instead of the cleaner alt()):
=alt(only(VDAA),below(only(VDAA)))
I don't have a solution for your second question, unfortunately, but hopefully the first one will help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello!
Thanks for your help!
And Yes, you solved my first question...
I'm now looking for the second and third questions!
Thank you so much!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If this calculation is independent of any selections, you might be able to get it done in script...
First, load a table of only the date and actual LOPP values (not zero or null).
Then, load from that resident and use the peek() function to add the correct number of previous values.
Finally, use this calculated value as your new column.
This approach won't work if you need this to be responsive to selections, though - in that case it'd probably have to be something complex involving RangeSum() and Below() with some sort of formula to try and figure out how many below() you need to go in order to catch the last four values. Possibly this could also work with set analysis that would only look at non-zero values for LOPP in the first place, and then you might be able to fish the last four out with more ease. I can't help you with the actual formula, it's a bit too complex to deal with in my free time, but good luck!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you post your sample data as spreadsheet? I could not open your App, as I have only PE version of QV.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, of course!
Please, find below all the codes I have in the project...
My scrip has two tabs:
One called Calendário, with the following script:
LET vStartDate = num(Floor(YearStart(Today(),-4)));
LET vEndDate = num(Floor(Today()));
LET vToday = num(Today());
LET vYesterday = num(Today()-1);
Calendar:
LOAD
Date#(date($(vStartDate) + RowNo() - 1), 'DD/MM/YYYY') as PERI,
QuarterName(Date#(date($(vStartDate) + RowNo() - 1), 'DD/MM/YYYY')) as QUAR
AUTOGENERATE
$(vEndDate) - $(vStartDate) + 1;
Another tab called Financeiro, with the following code:
Financeiro:
LOAD * INLINE
[
KKEY, PERI, RBVS, DDRB, RLCS, CBSV, REBR, DGCV, DGEA, PNRA, OROP, ODOP, RDEP, FINA, RFIN, DFIN, RNOP, RECE, DESP, RATP, PPIC, IRDE, PECE, RJCP, PACD, LOPP, STCK, ATTO, ATVC, CEEC, APFI, COAR, ESTO, ATVB, TRAR, DESA, OUTC, ARLP, APJV, AFCA, CAR1, EST1, ATB1, TRBD, DAN1, CCPR, OTNC, INVE, IMOB, INTA, DIFE, PATO, PACI, OSET, FORN, OBRF, EEFI, PCPR, DEJP, OUTR, PROV, PSAN, PNCI, EEF1, PPR1, OUT1, TRB1, AFAC, PRV1, PAC1, LCAA, PABP, PALI, CSRE, RECA, RERE, RELU, LPAC, AAPA, AACO, ORAB, AAC1, VDAA, VMAA, VMBA, VDAF, VAFA, VDAN
];
For each Sticker in 'AZUL4' //'GOLL4', 'AMAR3', 'GGBR4'
DemonstrativoResultados:
LOAD
'$(Sticker)' & @1 as KKEY, // Key para as tabelas
Date(Date#(@1, 'DD/MM/YYYY')+1, 'DD/MM/YYYY') as PERI, // Periodo
Num(@2) as RBVS, // Receita Bruta de Vendas e/ou Serviços.
Num(@3) as DDRB, // Deduções da Receita Bruta.
Num(@4) as RLCS, // Receita Líquida de Vendas e/ou Serviços.
Num(@5) as CBSV, // Custo de Bens e/ou Serviços Vendidos.
Num(@6) as REBR, // Resultado Bruto.
Num(@7) as DGCV, // Despesas Com Vendas.
Num(@8) as DGEA, // Despesas Gerais e Administrativas.
Num(@9) as PNRA, // Perdas pela Não Recuperabilidade de Ativos.
Num(@10) as OROP, // Outras Receitas Operacionais.
Num(@11) as ODOP, // Outras Despesas Operacionais.
Num(@12) as RDEP, // Resultado da Equivalência Patrimonial.
Num(@13) as FINA, // Financeiras.
Num(@14) as RFIN, // Receitas Financeiras.
Num(@15) as DFIN, // Despesas Financeiras.
Num(@16) as RNOP, // Resultado Não Operacional.
Num(@17) as RECE, // Receitas.
Num(@18) as DESP, // Despesas.
Num(@19) as RATP, // Resultado Antes Tributação/Participações.
Num(@20) as PPIC, // Provisão para IR e Contribuição Social.
Num(@21) as IRDE, // IR Diferido.
Num(@22) as PECE, // Participações/Contribuições Estatutárias.
Num(@23) as RJCP, // Reversão dos Juros sobre Capital Próprio.
Num(@24) as PACD, // Part. de Acionistas Não Controladores DR.
Num(@25) as LOPP // Lucro/Prejuízo do Período.
FROM
[https://lelli.000webhostapp.com/DataBase/$(Sticker).xlsx]
(ooxml, no labels, table is [Dem. Result.], filters(
Remove(Row, Pos(Top, 1)),
Transpose(),
Remove(Row, RowCnd(CellValue, 4, StrCnd(null))),
Remove(Row, Pos(Top, 1))
));
Join (DemonstrativoResultados)
LOAD
'$(Sticker)' & @1 as KKEY, // Key para as tabelas
'$(Sticker)' as STCK, // Sticker
Date(Date#(@1, 'DD/MM/YYYY')+1, 'DD/MM/YYYY') as PERI, // Periodo
Num(@2) as ATTO, // Ativo Total
Num(@3) as ATVC, // Ativo Circulante
Num(@4) as CEEC, // Caixa e Equivalentes de Caixa
Num(@5) as APFI, // Aplicações Financeiras
Num(@6) as COAR, // Contas a Receber
Num(@7) as ESTO, // Estoques
Num(@8) as ATVB, // Ativos Biológicos
Num(@9) as TRAR, // Tributos a Recuperar
Num(@10) as DESA, // Despesas Antecipadas
Num(@11) as OUTC, // Outros Ativos Circulantes
Num(@12) as ARLP, // Ativo Realizável a Longo Prazo
Num(@13) as APJV, // Aplicações Financeiras Avaliadas a Valor Justo
Num(@14) as AFCA, // Aplicações Financeiras Avaliadas ao Custo Amortizado
Num(@15) as CAR1, // Contas a Receber1
Num(@16) as EST1, // Estoques1
Num(@17) as ATB1, // Ativos Biológicos1
Num(@18) as TRBD, // Tributos Diferidos
Num(@19) as DAN1, // Despesas Antecipadas1
Num(@20) as CCPR, // Créditos com Partes Relacionadas
Num(@21) as OTNC, // Outros Ativos Não Circulantes
Num(@22) as INVE, // Investimentos
Num(@23) as IMOB, // Imobilizado
Num(@24) as INTA, // Intangível
Num(@25) as DIFE, // Diferido
Num(@26) as PATO, // Passivo Total
Num(@27) as PACI, // Passivo Circulante
Num(@28) as OSET, // Obrigações Sociais e Trabalhistas
Num(@29) as FORN, // Fornecedores
Num(@30) as OBRF, // Obrigações Fiscais
Num(@31) as EEFI, // Empréstimos e Financiamentos
Num(@32) as PCPR, // Passivos com Partes Relacionadas
Num(@33) as DEJP, // Dividendos e JCP a Pagar
Num(@34) as OUTR, // Outros
Num(@35) as PROV, // Provisões
Num(@36) as PSAN, // Passivos sobre Ativos Não-Correntes a Venda e Descontinuados
Num(@37) as PNCI, // Passivo Não Circulante
Num(@38) as EEF1, // Empréstimos e Financiamentos1
Num(@39) as PPR1, // Passivos com Partes Relacionadas1
Num(@40) as OUT1, // Outros1
Num(@41) as TRB1, // Tributos Diferidos1
Num(@42) as AFAC, // Adiantamento para Futuro Aumento Capital
Num(@43) as PRV1, // Provisões1
Num(@44) as PAC1, // Passivos sobre Ativos Não-Correntes a Venda e Descontinuados1
Num(@45) as LCAA, // Lucros e Receitas a Apropriar
Num(@46) as PABP, // Participação dos Acionistas Não Controladores as Part. de Acionistas Não Controladores BP
Num(@47) as PALI, // Patrimônio Líquido
Num(@48) as CSRE, // Capital Social Realizado
Num(@49) as RECA, // Reservas de Capital
Num(@50) as RERE, // Reservas de Reavaliação
Num(@51) as RELU, // Reservas de Lucros
Num(@52) as LPAC, // Lucros/Prejuízos Acumulados
Num(@53) as AAPA, // Ajustes de Avaliação Patrimonial
Num(@54) as AACO, // Ajustes Acumulados de Conversão
Num(@55) as ORAB, // Outros Resultados Abrangentes
Num(@56) as AAC1 // Adiantamento para Futuro Aumento Capital1
FROM
[https://lelli.000webhostapp.com/DataBase/$(Sticker).xlsx]
(ooxml, no labels, table is [Bal. Patrim.], filters(
Remove(Row, Pos(Top, 1)),
Transpose(),
Remove(Row, RowCnd(CellValue, 5, StrCnd(null))),
Remove(Row, Pos(Top, 1))
));
Join (DemonstrativoResultados)
LOAD
Date(Date#(@1,'YYYY-MM-DD'), 'DD/MM/YYYY') as PERI, // Periodo
'$(Sticker)' as STCK, // Sticker
Num#(@2, '#,##0,00;-#,##0.00', '.', ',') as VDAA, // Valor da ação na abertura
Num#(@3, '#,##0,00;-#,##0.00', '.', ',') as VMAA, // Valor mais alto na ação no dia
Num#(@4, '#,##0,00;-#,##0.00', '.', ',') as VMBA, // Valor mais baixo da ação no dia
Num#(@5, '#,##0,00;-#,##0.00', '.', ',') as VDAF, // Valor da ação no fechamento
Num#(@6, '#,##0,00;-#,##0.00', '.', ',') as VAFA, // Valor da ação no fechamento ajustado
Num#(@7, '#,##0,00;-#,##0.00', '.', ',') as VDAN // Volume negociado da ação
FROM
[https://query1.finance.yahoo.com/v7/finance/download/$(Sticker).SA?period1=1431043200&period2=1593907200&interval=1d&events=history] //5 Years Period
(txt, codepage is 1252, no labels, delimiter is ',', msq, filters(
Remove(Row, Pos(Top, 1)),
Remove(Row, RowCnd(CellValue, 2, StrCnd(contain, 'null')))
));
Financeiro:
Concatenate (Financeiro) LOAD
KKEY,
PERI,
RBVS,
DDRB,
RLCS,
CBSV,
REBR,
DGCV,
DGEA,
PNRA,
OROP,
ODOP,
RDEP,
FINA,
RFIN,
DFIN,
RNOP,
RECE,
DESP,
RATP,
PPIC,
IRDE,
PECE,
RJCP,
PACD,
LOPP,
STCK,
ATTO,
ATVC,
CEEC,
APFI,
COAR,
ESTO,
ATVB,
TRAR,
DESA,
OUTC,
ARLP,
APJV,
AFCA,
CAR1,
EST1,
ATB1,
TRBD,
DAN1,
CCPR,
OTNC,
INVE,
IMOB,
INTA,
DIFE,
PATO,
PACI,
OSET,
FORN,
OBRF,
EEFI,
PCPR,
DEJP,
OUTR,
PROV,
PSAN,
PNCI,
EEF1,
PPR1,
OUT1,
TRB1,
AFAC,
PRV1,
PAC1,
LCAA,
PABP,
PALI,
CSRE,
RECA,
RERE,
RELU,
LPAC,
AAPA,
AACO,
ORAB,
AAC1,
VDAA,
VMAA,
VMBA,
VDAF,
VAFA,
VDAN
Resident DemonstrativoResultados;
Drop Table DemonstrativoResultados;
Next Sticker
With the following codes, you will be able to download the database just loading the scripts, once they are all online... this is how I'm loading my scripts.
On my QlikView screen, I have a chart (table), with the following columns:
PERI (Dimension)
Expression:
Sum(VDAA)
Btw, if you can't load my online DataBases, please find attached the Excel file called AZUL4, with the company's financial report.
Just it!!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Any news?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your support,
But I don't know how to do it, and it's dependent on selections, once I'll have lots of companies to select.
Thanks for your help!
