Chart View based on Date Aggregation

Alok_Behria · 10-25-2021 09:44 AM

Hello All,

I have following tables which shows stock buy and sale date. here , I want to create histogram chart to show profits group by month and user should able to select month from the chart option.

Regards
Alok Beheria

Marc_Dillon

Are you actually requesting a feature here, or are you asking a question? There is new charting stuff in the works already, so any chart-related requests should probably be directed to that thread (maybe).

To chart aggregated data, you need to start by creating a new Table on which to aggregate your data, then create a chart on that new Table. It sounds like each record in your aggregate Table should correspond to one month, to get the chart you’re looking for.

Alok_Behria

Thanks Marc for your inputs, I am not asking the new feature , but I want to add charting option to my App for which Users can see the performance of different stocks.

Also, I have read somewhere that virtual column can also be used to create aggregate values which in turn can be plot on the charts

Thanks

Regards
Alok Beheria

Alok_Behria

Can someone here help me get some kind of “How to” to make this work. Since I have new here, I am not sure how to start with this.

Regards

Marc_Dillon

Is there something about my suggestion above that you did not understand?

Alok_Behria

Hi Marc,

I am still not sure how to frame a formula. I am coming from oracle database background and if I need to do the sum based on some group by syntax then it’s pretty straight forward like select month , name , sum(profit) from table_name group by month etc. Here, I am not sure , in following table , what expression to write to do the sum of ABS Profit group by month. In following snippet I want to show sum(Abs profit) by month and show them up in histogram format.

Marc_Dillon

You would need to use a SUM(SELECT()) expression.

Something like:

SUM( SELECT(
  Table[Abs Profit] ,
  [Sell date] = xxx
) )

The “xxx” will need to be a variable month value of some sort, which is why I suggested creating a Table where each record is one month. I would use a formatted date to text value to include the year as well.

So something like this for the new Table, you can start by just creating this manually:

Then add a virtual column to that new Table to hold the expression above, and change the condition to something like:

...
  TEXT( [Sell Date] , "mmm-yy" ) = [_THISROW].[month_year]
...

Alok_Behria

Thanks so very much for your help here. I tried to create separate table extracting month using TEXT function. I did also create a virtual column Aggregate by month and used below formula to find sum of profits against a particular month , but chart does not show any data here.

SUM( SELECT(
Table[Abs Profit] ,
TEXT( [Sell Month] , “mmm-yy” ) = [_THISROW].[Sell Month]
) )

Marc_Dillon

It seems you misunderstood the date formatting with the TEXT() expression.

There was no reason for you to add the [Sell Month] column like that, but if you do want to use it, then you don’t need the TEXT() anymore, just do a straight comparison. [sell month] = [sell month]

Is your Table really called “Table”, or…?

Alok_Behria

Hi Marc,

Any comment you like to make here, I am still not able to show the month sum by total profit made in a particular month. here, it is showing the total profit generated in 3 months rather then showing month wise. I tried to use group by in google sheet and it’s showing the data correctly.

Regards

Marc_Dillon

I’ve already made lots of comments, pretty much showing you exactly what you need to do, but you seem to not be understanding or following them exactly. I will copy them down here, please review it all again and ask specific clarification questions if you need to.

Marc_Dillon:

Something like:
SUM( SELECT(
  Table[Abs Profit] ,
  [Sell date] = xxx
) )
The “xxx” will need to be a variable month value of some sort, which is why I suggested creating a Table where each record is one month. I would use a formatted date to text value to include the year as well.

So something like this for the new Table, you can start by just creating this manually:

Then add a virtual column to that new Table to hold the expression above, and change the condition to something like:
...
  TEXT( [Sell Date] , "mmm-yy" ) = [_THISROW].[month_year]
...

The complete expression for the VC in the new table would be:

SUM (SELECT(
  your-table-name[Abs Profit] ,
  [Sell Month] = [_THISROW].[month_year]
) )

or:

SUM (SELECT(
  your-table-name[Abs Profit] ,
  TEXT( [Sell Date] , "mmm-yy" ) = [_THISROW].[month_year]
) )

Then maybe you should just use charting in the spreadsheet and be done with it. Appsheet charting is not that great.

Alok_Behria

My bad, I managed to copy the wrong one. I created this formula on the virtual column.

SUM(SELECT(
Perf_month_wise[Abs Profit] ,
TEXT( [Sell Month] , “mmm-yy” ) = [_THISROW].[Sell Month]
) )

Alok_Behria

Thanks Marc .I think , I finally managed show the charts month wise, but here, the horizontal line shows the total for the month and I want this to show as “Aug 21”, “Sep 21”, “Oct 21” and the vertical line should show “total for the month”.

Regards

Marc_Dillon

The text displayed at the bottom is the Label value for the record, so just change the Label column on the new Table.

Alok_Behria

Thanks Marc for your advice , I was able to sort this issue by creating a virtual column on which I crated an expression “TEXT( [Sell Date] , “mmm-yy” )” . Here, I am looking to sort the months as the chart showing “Aug 21”, “Oct 21” and “Sep 21”. I tried to use orderby clause on the column , but got error. I would appreciate if you could offer your help here.

Regards

Marc_Dillon

Alok_Behria

Thanks Marc for your quick turn around , but when I select Col series chart , it no longer show the column for Month and chart display wrong data.

Regards

Marc_Dillon

You weren’t already using “col series”?

Alok_Behria

Thanks very much … No, I was using Histogram chart. Here , if I change the virtual column to DATE , then it will show date in Ascending order in the form 21/09/21,21/10/21 ,21/11/21 instead of showing “Aug 21”,“Sep 21” and so on… I was thinking , if I convert it to date and show back in form of TEXT , but ascending order.

Regards

Marc_Dillon

I can’t help you if I don’t know what you have set up. And since you apparently aren’t doing what I suggested, I have no idea what you have set up.

Alok_Behria

Thank , I used histogram chart and not sure , where did you get a impression of me using col series chart. Here, I am only using histogram chart to show dates in ascending order . Everything is set nicely except the dates order.

Regards

Alok_Behria

Hi Marc,

If you could suggest anything on it , It would be a great help … I am just thinking of using order by on dates column which is converted to TEXT , but not sure how to frame the expression. I used expression TEXT( [Sell Date] , “mmm-yy” ) to convert dates into TEXT form but then while showing this up on the chart , it’s not showing dates in order.

Regards