creating an area (line) chart with periods

Hi, I am following a step by step instruction to create an area chart in OS. 

Although this video is clear and the result is as explained, I cannot make my graph, as my dataset is in periods (date from and date until). Is there a way in OS to work with periods? So from 1-1-1987 until 28-2-1988 the value is 8, than that records counts for the whole period (all days in between) and that should be elaborated in the graph. 


I included an excel to explain my issue.

Hi Coen,

If you would be able to share our application as an OAP file then the community can more easily try to help you.

Maybe also a sketch what you expect to see in the line chart, I noticed your date periods are overlapping so do, not fully understand what you try to get plot in the chart.

Regards,

Daniel

Hi Daniel, I have no oml availabe as I don't know how to realize the chart in a form. I included the excel, which has data and a related chart (the sketch) as well. 

The data is in column a to f (that is the source entity for the chart), and I created some functionality (from column i and further), to count the daily totals from column a to f. Based on that, I created a chart. 

This chart is what I need to create in OS. So I guess I need somehow to create an aggregate that sums op the totals on daily basis, but I have no idea how to do that. 

Hope you can help me out:-)

Best..Coen

Hi Coen,

So if I understand correct the data in column a to f is the source data.

The data you generated in column i and further is derived from the source data, so that you can get a sum per date and show the sum / date in a line chart. The data that you are interested it is in column PQ_CRB_Positive.

This is what I would do:

  1. clean the spreadsheet to only have column a to f, make sure the date columns are formatted as dates.
  2. drag & drop the spreadsheet in on the Data tab to bootstrap the data.
  3. create a new entity DataPoints with attributes: Date and Value
  4. create a server actions that first reads all data from the bootstrapped entity.
  5. use an SQL to delete all records from DataPoints
  6. use a foreach over the bootstrapped aggregate and create for each date between PQ_StartDate and PQ_EndDate a record in entity DataPoints with PQ_StartDate and PQ_CRB_Positive as Date and Value.
  7. Create a screen that will have a line chart, and in the preperation get all data from the DataPoints entity, you can do a group by on the Date to get the sum for each date.
  8. Configure the chart element with the data from the DataPoints aggregate.

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Coen,

So if I understand correct the data in column a to f is the source data.

The data you generated in column i and further is derived from the source data, so that you can get a sum per date and show the sum / date in a line chart. The data that you are interested it is in column PQ_CRB_Positive.

This is what I would do:

  1. clean the spreadsheet to only have column a to f, make sure the date columns are formatted as dates.
  2. drag & drop the spreadsheet in on the Data tab to bootstrap the data.
  3. create a new entity DataPoints with attributes: Date and Value
  4. create a server actions that first reads all data from the bootstrapped entity.
  5. use an SQL to delete all records from DataPoints
  6. use a foreach over the bootstrapped aggregate and create for each date between PQ_StartDate and PQ_EndDate a record in entity DataPoints with PQ_StartDate and PQ_CRB_Positive as Date and Value.
  7. Create a screen that will have a line chart, and in the preperation get all data from the DataPoints entity, you can do a group by on the Date to get the sum for each date.
  8. Configure the chart element with the data from the DataPoints aggregate.

Regards,

Daniel

Hi, I have the entity already in my database, see uploaded oml. The enity is AssetPQ. So, I created a new entity (PQChart) having a Date and value attribute. I created a server action that reads the data from the Asset PQ attribute (sa_PQChart). I dropped an SQL statement, having the SQL condition Delete * from PQChart. However, this results in an error, Output structure must be set in SQL1. 

Got stuck here (I am a beginner:-), but via Datatab/structures I don't no what steps to follow.I also never used a foreach statement (step 6). Is there a simple instructionvideo or text concerning this? (can't find an applicable one)


Regards, Coen


Hi,

That is a stupid thing of OutSystems, the sql widget always need an output structure even for a delete.

Create a structure let's name it Dummy of type text and add it as output parameter in the sql widget.

Daniël Kuhlmann wrote:

Hi,

That is a stupid thing of OutSystems, the sql widget always need an output structure even for a delete.

Create a structure let's name it Dummy of type text and add it as output parameter in the sql widget.

fixed the stupid thing:-), and got stuck again. 

I created a structure (str_PQChartOutput) with one attribute (output, datatype = text).

Then I created a for each statement with the record list being the aggregate I created (GetAssetPQS.List)

I created a cycle to the serveraction "CreatePQChart". 

Here I got stuck. I inserted the source (GetAssetPQS.List.Current.AssetPQ), but no idea what to put in the mapping (I inserted nullidentifier() as Id, just to lose the error-message). I need to have a record for every day, while I only have a period (date from and date until) in the source entity. 

I included the .oml, in case I did not mentioned some relevant details:-)


Hi,

  1. Create a local parameter CurrentDate of Date.
  2. Drop an assign between the ForEach and the CreatePQChart action.
  3. Assign to CurrentDate the PQ_StartDate of the current record of the aggregate
  4. After the assign drop an If statement with Date < PQ_Enddate of the current record of the aggregate
  5. In the CreatePQChart action in the properties panel set the Date value to the CurrentDate and set the value to the PQ_CRB_Positive 
  6. Add an assign widget after the createPQChart and set CurrentDate to AddDays(CurrentDate,1)
  7. connect the assign to the IF statement
  8. and the IF statement back to the foreach

Sorry can't look at oml as typing this on phone.

Regards,

Daniel


Hi, I had to rebuild the whole thing as I am working on 2 computers and was not able to upload the oml due to an error). 

But: I followed every step you elaborated above. 


Still, the entityTempChartData entity (having a date and a value attribute) remain empty. This can be caused by the fact that the action is never called off. 

You last step, to create a screen having the chart: Don't know what to fill in here as the needed steps differ from the instruction videos.

Can you tell me what to fill in to have the graph working (and have the entity filled with applicable data)?

Hope you can help me with the last step...:-)

Hi Coen,

Step-by-step you are getting to the end!

Regarding sa_ChartData server action:

The condition on the if needs to be swapped the True and False branch should be the other way around:

As a best-practice let your flow always go with the clock (see i moved the currDate assignment and the CreateorUpdateTempChartData Server action to now go with the clock.

On the TempChartData entity change the Value attribute datatype from Text to Integer

Then regarding the chart:

  1. You have to add a Preparation to the POChartScreen
  2. Drag the TempChartData entity in it to create an aggregate that fetches all the data from it.
  3. Before the aggregate include the call to the sa_ChartData server action
  4. Set the label property to GetTempChartData.List.Current.TempChartData.Date
  5. Set the value property to GetTempChartData.List.Current.TempChartData.Value

PS you have bootstrap multiple versions of different spreadsheets for different versions.

They all run on publish, some of them adding to the same entity. When I debugged them there where problems, so I noticed the AssetsPQ entity did not get filled moste likely to referential key violiations  on PSPId, AssettId or ProductId.

Those entities need to have valid records prior to you adding AssetPQ records. I am not sure how you can assure that the Identifiers for BSPId, AssetId, and ProductId will match the once in OutSystems as you have the Identifiers of those entities set to Autonumber. So any time you empty those entities the bootstrap will add new records with new numbers.

In short, you have to cleanup your bootstrap logic and debug/test it properly to confirm you get all data correctly loaded. Maybe you have to reconcider if this should be done at publish time, maybe better you kick it of by a button in your application?

Regards,

Daniel

Processing Upload...

Hi Daniël, 

I followed all steps (and good one: clockwise flows), and everything seems fine from the OS devellopment module. However, when I open the PQ Chart Screen, an internal server error pops up. 

The enitty TempChartData still has no records to show.


(concerning the PS: I imported some data from an application in this one, for testing/prototyping purposes. This was a one time action. They should all have the correct foreign keys. but reading your post you doubt that. What I did was importing the records per entity, using the keys that were generated, and replaced "my" keys (from the original application) with the "OS- keys". No new records will be imported, they will all be generated within the OS-application. However, I really need to study on the debug thing, as I think it is essential for understanding things that go not as planned. I'll pick it up soon!)

 Best...Coen

Hi Coen,

As I only get the OML, it comes with no records in the entity, and thus the bootstrap is performed on publishing the module. That causes problems on my site to check / test your code.

If you could provide a module with correctly working bootstrap, that it is easier to help you. 

PS. remember that you current way of working, will require you to update the OS-keys in your spreadsheets when it is time to move this to QA and again when you move it to PRD.

Regards,

Daniel


Daniël Kuhlmann wrote:

Hi Coen,

As I only get the OML, it comes with no records in the entity, and thus the bootstrap is performed on publishing the module. That causes problems on my site to check / test your code.

If you could provide a module with correctly working bootstrap, that it is easier to help you. 

PS. remember that you current way of working, will require you to update the OS-keys in your spreadsheets when it is time to move this to QA and again when you move it to PRD.

Regards,

Daniel


just send you a pm...