Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
brunolelli87
Creator II
Creator II

Show the value that corresponds to a specific date on a chart

Hello folks, 

I have a bar chart (or a Straight Table) with the =Date(DVPG) as Dimension and 2 expressions, that are:

  • sum(LOPP) and,
  • =avg({<DVPG ={'$(=AddYears(Date(DVPG), -3))'}>} LOPP)

But the second expression is not working, it's just showing -

My intention with the second expression is to show the LOPP of 3 years ago (day-by-day).

Check the following example:

DVPGLOPPmy question
27/05/2020 R$   829,45 R$      299,28
26/05/2020 R$   388,41 R$      976,54
25/05/2020 R$   451,40 R$      910,95
 …  … 
27/05/2017 R$   299,28 R$      968,37
26/05/2017 R$   976,54 R$        72,26
25/05/2017 R$   910,95 R$      732,61
 …  … 
27/05/2014 R$   968,37 
26/05/2014 R$     72,26 
25/05/2014 R$   732,61 

 

I also attached a QlikView file with some sample data.
I'm using a personal edition of QlikView so I won't be able to open any file from you!

 

Why I'm getting those 0, and sometimes depending on my trials I'm getting -

What am I doing wrong?

 

How can I show the result of LOPP of 3 years ago (day-by-day)?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Check if this works

View solution in original post

14 Replies
sunny_talwar

Check the attached

brunolelli87
Creator II
Creator II
Author

I’m using a personal edition of QlikView... would you mind to send me the codes?

Thanks
sunny_talwar

Script

// Carrega a Taxas
Indices:
LOAD 
	 Date#(@1,'DD/MM/YYYY')									as  DVPG,		// Data a ser considerada 
     Num#(@2, '#.##0,00;-#.##0,00', ',', '.')				as  IGPD,		// Valor, em pontos, da IGP-DI
     Num#(@3, '#.##0,00;-#.##0,00', ',', '.')				as  IDTS		// Valor da taxa Selic
FROM
[https://www.lellidesp.com.br/Empresas/Taxas.csv]
(txt, utf8, no labels, delimiter is ';', msq, filters(
Remove(Row, Pos(Top, 1))
));


LOAD  

	Date(@1, 'DD/MM/YYYY')				as	DVPG,	// Periodo.
	Num(@26*1000)						as  LOPP	// Lucro/Prejuízo do Período.
	     
FROM
[https://www.lellidesp.com.br/Empresas/DR/AZUL4.csv]
(txt, utf8, no labels, delimiter is ';', msq, filters(
Remove(Row, Pos(Top, 1)),
Remove(Row, RowCnd(CellValue, 5, StrCnd(null)))
));

tmpDate:
LOAD Distinct DVPG
Resident Indices;

Concatenate (tmpDate)
LOAD Distinct DVPG
Resident AZUL4;

AsOfDate:
LOAD DISTINCT DVPG as AsOfDVPG,
	 Date(AddYears(DVPG, -3), 'DD/MM/YYYY') as DVPG,
	 '3 Year Ago' as Flag
Resident tmpDate;

Concatenate (AsOfDate)
LOAD DISTINCT DVPG as AsOfDVPG,
	 DVPG as DVPG,
	 'Current' as Flag
Resident tmpDate;

DROP Table tmpDate;

Now create a chart with

Dimension
AsOfDVPG

Expressions
=Avg({<Flag = {'Current'}>} LOPP)
=Sum({<Flag = {'Current'}>} IDTS)
=Sum({<Flag = {'Current'}>} IGPD)
=Avg({<Flag = {'3 Year Ago'}>} LOPP)
brunolelli87
Creator II
Creator II
Author

It seems to be working.
I'll try to implement it in my project and I'll let you know in a few minutes.

 

Thank you so much Sunny,

Bye

brunolelli87
Creator II
Creator II
Author

Hello Sunny,

Thanks for your help, but I'm so inexperienced, and I couldn't adapt it to my project.

Would you mind helping me to do it?

 

Please find attached my project.

sunny_talwar

I see you tried creating the table, but I couldn't find the script you used for this?

image.png

brunolelli87
Creator II
Creator II
Author

Yes, I'm sorry!

I tried, I did my best, but once I couldn't find a solution, I just removed the script!

brunolelli87
Creator II
Creator II
Author

And once I have no idea about how to do it, I thought that was no reason to send you my "bad" trials

sunny_talwar

Sorry, I am not sure I follow?