Dynamic line chart with quickchart date format

Hi everyone,

I´ve been succesful with this post: Radial Gauge / "Meter" - Inline Dynamic Icon - How to create?this post to make a dynamic gauge icon that adjusts according to a specific value from one row.

Im working now on a Line Chart with Quickchart,

Im using SELECT to get the list of all the column.

This is the formula I´m using now and the result I get:
CONCATENATE(“https://quickchart.io/chart?c={type:‘line’,data:{labels:[”,SELECT(Reporte Aguadas[Fecha],true),"],datasets:[{label:‘Nivel’,data:[",SELECT(Reporte Aguadas[Nivel en mm],true),"],borderColor:‘green’}]}}")

Result URL: https://quickchart.io/chart?c={
type:‘line’,
data:{
labels:[08/28/2020 , 08/28/2020 , 08/30/2020 , 08/29/2020 , 08/30/2020 , 08/31/2020 , 08/29/2020 , 08/29/2020 , 08/29/2020 , 08/29/2020 , 08/31/2020 , 08/31/2020 , 08/31/2020 , 09/02/2020 , 09/03/2020 , 09/03/2020 , 09/11/2020 , 09/03/2020 , 09/03/2020],
datasets:[{
label:‘Nivel’,data:[1500 , 1850 , 2500 , 2450 , 1800 , 3000 , 1500 , 550 , 1800 , 3000 , 1900 , 1800 , 3000 , 350 , 900 , 1200 , 1600 , 1500 , 2100],borderColor:‘green’}]}}

I see the dates are not shown, I suppose I have to format that too? It seems as it actually made the division of the date= 08/28/2020=.0001414…?

Any ideas would be very appreciated.

Thanks!

Solved Solved
0 4 823
1 ACCEPTED SOLUTION

is equivalent to just

Reporte Aguadas[Fecha]

It looks like you need to put each label value inside of single quote in order to stop the forward slash from being evaluated as division.

Wrap the above in TEXT() to turn that list into simple text. Then we’ll substitute the commas, with a comma with single quote on either side. Then to finish off, concatenate a single quote to beginning and end.

" ’ " , SUBSTITUTE( TEXT( Reporte Aguadas[Fecha] ) , “,” , " ’ , ’ " ) , " ’ "

Replace your SELECT(Reporte Aguadas[Fecha],true) with the above.

View solution in original post

4 REPLIES 4

is equivalent to just

Reporte Aguadas[Fecha]

It looks like you need to put each label value inside of single quote in order to stop the forward slash from being evaluated as division.

Wrap the above in TEXT() to turn that list into simple text. Then we’ll substitute the commas, with a comma with single quote on either side. Then to finish off, concatenate a single quote to beginning and end.

" ’ " , SUBSTITUTE( TEXT( Reporte Aguadas[Fecha] ) , “,” , " ’ , ’ " ) , " ’ "

Replace your SELECT(Reporte Aguadas[Fecha],true) with the above.

Hey @Marc_Dillon, thanks again for awesomeness.

I´m sorry I didnt share my complete formula in my last post. I will actually be using the SELECT formula to filter some of the data.

This is the actual formula: CONCATENATE(“https://quickchart.io/chart?c={type:‘line’,
data:{labels:
[ ’ “,SUBSTITUTE(TEXT(SELECT(Reporte Aguadas[Fecha],([Aguada]=“Carolina”),true)), " , " , " ’ , ’ “),” ’ ],
datasets:[{label:‘Nivel’,data:[”,SELECT(Reporte Aguadas[Nivel en mm],([Aguada]=“Carolina”),true),”],borderColor:‘green’}]}}")

I had to eliminate one of the double Quotes" at the begininng and end to make it work:

’ " , SUBSTITUTE( TEXT( Reporte Aguadas[Fecha] ) , “,” , " ’ , ’ " ) , " ’

Thanks again!

You know how SELECT formula brings back a list of values, possibly duplicates.

Apparently QuickCharts, cannot handle duplicate X values… Does anyone know a workaround for this?

How it should be: 3X_5_6_5660788a0aef8f95d16808c97d24cd521ebb91c9.png

How it is:

Same data and formula for both graphics. The first built with REF_ROW, the second one with QuickChart:
https://quickchart.io/chart?c={type:‘line’,data:{labels:[ ’ 29/8/2020 ’ , ’ 30/8/2020 ’ , ’ 31/8/2020 ’ , ’ 2/9/2020 ’ ],datasets:[{label:‘Nivel’,data:[2450 , 1800 , 3000 , 350],borderColor:‘green’}]}}

Well, there is the UNIQUE() expression.

However that’ll only fix the labels, then you’ll run into extra data points, and data points paired with the wrong labels.

I don’t see any obvious ways to get around that right off the bat, simple or otherwise.

How would you expect 2 data points for the same date to be displayed on a line graph such as this?

Top Labels in this Space