Chart Data Source with groupings and merged with

[msamandadianne]

I’d like to make a line chart into a timeline that shows all the dates associated with a project.

These dates are sourced from different data types which have the Project Name and Date fields (among others).

For example, Expenses (Project Name, Date, Amount) and Income (Project Name, Date, Amount). There are two other data types that I want to include as well.

Doing one data type for the data source works and it looks like this

Data Source:
Search for Expenses (constrain to project): grouped by Date (Group By Date, Type - Day, Interval - 1, Starting Date - Search for Expenses (constrain to project , sort by date, ascending) : first item’s Date , Aggregation - Count)

Value Expression: Current Point’s Count

Label Expression: Current point’s Date: formatted as 12/02/19

But I need to also include the incomes and more.

Attempting to follow the same logic as above but then adding merged with… yields a red expression.

Search for Expenses: grouped by Date merged with Search for Incomes: grouped by Date

Any suggestions?
Thanks!

[boston85719]

usually the red expression is because the data is not of the same type.

So trying to merge incomes with expenses wouldn’t be possible

If you need both sets of data for the line graph, you would need two separate sets of data to then plot on the same line graph

What you might be able to do is create a new data type that has a data field for is_expense / is_income as a Yes/No

is_income = Yes
is_expense = No

Then when you are setting your chart datasource it is of the same data type…it would need the other data fields as well ( project name, date, amount )

Then on your chart there should be a way to set conditionals on the label colors based on the value of the is_income or is_expense data fields.

[msamandadianne]

I’ve decided to create a new data type and save all the appropriate dates to it.

Now the question is how to display the data.

I don’t mind a bar chart style but the x axis needs to be a timeline and not skip any dates. Then a bar on the appropriate date.

I’ll think about this.

EDIT:
I’ve managed to create a crude a axis.

Now (1) how do I get my list of dates to display in the cells that have the matching date counter? Or, really the count of the list of dates that match the date counter. This will most likely be 1 in my case but it could be more.

(2) How do I group the dates by the chosen time scale and then display the count for the grouping in the appropriate cell?

[boston85719]

Those are all good questions. I also find “speaking outloud” on the forum post posing questions about how to build my app helps me to come up with some solutions after a bit further digging.

After attempting some solutions let me know if you need any further help. Good luck.

[msamandadianne]

Someone may have an answer for me

[boston85719]

What have you tried so far?

[msamandadianne]

Just trying different text expressions but that more of experimentation.

I wish there was a :matches expression. I thought I remembered seeing some kind of matching function but it’s not coming up in my experimentation.

It’s a list of 18 dates (in this example) all saved as one thing I’m calling Timeline (under my Projects datatype) which is easily sorted into ascending order.

My brain is thinking this logic:

When the time scale is 1, I want to take the first date in the list, call it date[1], and test it against date counter[1] . Which based on the way I set up the counters, should be a match. Basically its the beginning of the timeline. Then date[2] test against date counter [x] until there is a match. Then whatever x that is the index of where a count of 1 will be. Continue this until all the list of dates are done. Then I’ll know where all the count’s are. Then they can be summed if they fall on the same index. Then the sum can be displayed visually with a bar at the appropriate index.

When the time scale is 7, do a similar thing but the test would be to see if date[x] falls in the range of dates created by the time scale.

That’s what my brain wants but no what Bubble wants haha

[boston85719]

I would assume if the database is structured correctly you should be able to have a timeline created by referencing the dates, which seems to be already occurring…but you would then be able to construct the “bar” to be referencing the date but pulling from that bit of data the corresponding amounts.

So it would be on a “bar” do search for amounts with constraints of date = this date.

To get this date you would need a custom state that gets set potentially to use a reference for the date that you want to match the amount data to…this may be easy enough as you mentioned you have the dates in a chronological order ( ie: date 1 = item # 1 and date 12 = item #12 ), so it should be simple enough to have the list of dates as a custom state ( which actually is of type amounts because the dates are a data field in the amount data type )…

Another thing that could help is to use a repeating group that is hidden and utilize the current cell’s index value ( which is simply a number that is really a ordering of things in the r.g. ) to help make the connections between date 1 and amount 1 etc.

[msamandadianne]

I think I see what you mean, somewhat.

My DB is set up like this.

I’m thinking maybe set up a new datatype called Timeline with fields of Date and Project Name.

That will probably solve the problem.

[msamandadianne]

With a separate data type for the timeline, I’m able to use Chart Tools and make a timeline. It’s not perfect (the date labels don’t match, so I just made them white) But it’s doable.