Topic: Using a PieChart

Hello all
I just finished an app to manage my family bank accounts from a project by Derek (Thanks again Derek). It works perfectly. I was suggested to add a PieChart (which I had not considered). The suggestion proposes to distribute in this pie chart to count the occupancy rate by category (eg Food -> 60, Leisure -> 20, Petrol ->15 years so on), count the number of times each category appears in an account.
These categories are stored in an Imputations table (Imputations and Qty fields), Qty being the result to obtain to populate the pie chart.
I was again inspired by an example of Derek (PieChart) to move forward. But I'm stuck getting this total by category and displaying it in a TableGrid(There are 15 in total) (See Example file).
Thank you in advance for your help.
JB

Post's attachments

Attachment icon Example.jpg 63.34 kb, 29 downloads since 2023-05-27 

Re: Using a PieChart

Hi


Unfortunately, from the photo and description it is not entirely clear what the problem is and how to help.
But it looks beautiful.
An example is desirable.

Re: Using a PieChart

Hello Sparrow, thanks for your interest.
You're right:
Napoleon said: "a good sketch is better than a long speech".
Hope this will help you.
Please find in attachment what I'm looking for (using a pie chart).
I began to find a clue with a Select Distinct ... but I'm not very satisfied.
Perhaps that with a new eye... I'am using MVD 6.6.

PS: For those who would like to use this code, don't forget to quote Derek who helped me a lot on this project.

JB

Post's attachments

Attachment icon TEST_COMPTE.rar 604.47 kb, 109 downloads since 2023-05-30 

4 (edited by sparrow 2023-05-30 20:00:48)

Re: Using a PieChart

Hi,


Here are two versions of a possible request:
1. Shows only those categories that are in the movement of the account.

SELECT imputation AS Cat, count(*) AS Cnt
FROM transactions 
INNER JOIN Imputations ON transactions.id_Imputations=Imputations.id
WHERE DATE(tdate) BETWEEN '2023-05-01' AND '2023-05-31'
GROUP BY Imputation

2. It will show all categories, but if there was no movement in the category, it will show - 0.

SELECT imputation AS Cat, IFNULL(c1.c,0) AS Cnt
FROM Imputations
LEFT JOIN (SELECT id_Imputations, count(*) AS c 
                    FROM transactions 
                    WHERE DATE(tdate) BETWEEN '2023-05-01' AND '2023-05-31' 
                    GROUP BY id_Imputations) c1 ON c1.id_Imputations=Imputations.id

I added a selection by date to both.
If you need anything else please write.
I did not understand what the Qty column is for. It is calculated at any time.
Queries are applicable to both table and graph.


Look at procedure frmStats_OnShow and procedure frmStats_DateTimePicker1_OnChange
Responds to changing check, year, month in combobox

Post's attachments

Attachment icon pie-fix.zip 489.96 kb, 100 downloads since 2023-05-30 

Re: Using a PieChart

Hello Sparrow
Thank you for the speed of your answer.
Tell me, do you ever sleep?
Impeccable your two codes (DateTimepicker and Form1.OnShow).
I had to struggle to fix the syntax errors because of parity quotes. It's good now.
When I implement in the general script, I have an error message: "Access violation" with the abstruse messages of Windows (Error in the module ...) implied 'Make your own way to find the place'.
Do you have an example of integration (Form1.OnShow for example). On my side I will search while using a code from Derek to display the pie chart.
Thanks again for your help.
Cordially
J.B.

6 (edited by sparrow 2023-05-31 12:51:06)

Re: Using a PieChart

Hi


These are not two codes, this is one code in two places for different events. Basically it is possible to enter it in action of the SQL button for search.
Sorry, I forgot to write about quotes.

p.s.
That's right.

//*** Cache le menu si on utilise l'option 'styles'
begin
  Form1.Menu := Nil;
  Form1.tablegrid1.dbpopupmenu.Items[0].Caption := 'Afficher l''enregistrement';
  Form1.tablegrid1.dbpopupmenu.Items[3].Caption := 'Copier la cellule';
  Form1.tablegrid1.dbpopupmenu.Items[4].Caption := 'Copier la ligne';
  Form1.tablegrid1.dbpopupmenu.Items[5].Caption := 'Tout copier';
  Form1.tablegrid1.dbpopupmenu.Items[7].Caption := 'Rechercher';
end.

Re: Using a PieChart

Salut Jean, привіт Sparrow,
Just had a quick look at this topic and was wondering if you could do it like this (please see attached).
I couldn't understand how you would show both 'transactions.tpaidin' and 'transactions.tpaidout' on the same pie chart so decided it was easier to show them on discrete pie charts.
I have done it directly off 'form1' rather than use form 'frmstats' simply because it was easier to check the data against the existing transactions grid but the process would be just the same if you use 'frmstats'.
Just click again on the 'PieChart' button to hide the pie charts.
I've not had much time to test it so if it's of any help, you'll need to be a lot more thorough than I have been (particularly with the date selections (i hate working with dates!)).
Hope you're keeping well.
Derek.

Post's attachments

Attachment icon TEST_COMPTE2.zip 506.93 kb, 117 downloads since 2023-06-01 

Re: Using a PieChart

Hello Derek
Thank you for your help regarding PieChart.
By tweaking (hardly) the code proposed by Sparrow, I finally managed to get what I wanted.
But your code is much faster and more efficient.
You're right, you might as well display the pie chart directly on the main Form, I removed the form dedicated to displaying the pie chart, this lightens the overall code.
Thank you again for your helpful help.
J.B.
By the way, a piece of information: can we do operations between footer caption (example paid in - paidout) or do we have to go through a calculated field? I tried but I get format errors with FormulaValue.

Re: Using a PieChart

Hello All

I found the solution to perform calculations between footers of two columns and it works:

     Recipes := Form1.TableGrid1.Columns[5].Footer.FormulaValue;
     Expenses := Form1.TableGrid1.Columns[4].Footer.FormulaValue;
     Balance := (Form1.TableGrid1.Columns[5].Footer.FormulaValue)- (Form1.TableGrid1.Columns[4].Footer.FormulaValue);
     Form1.Edit1.Text:= FloatToStr(Balance);

I declared Receipts, Expenses as a variable of type Extended.

JB

10 (edited by sdpc62 2023-06-03 09:51:25)

Re: Using a PieChart

Hello jean hello Derek, I tried derek's 'TEST_COMPTE2.zip' and I can't display the income on the graph. I have yet entered a movement in + but as the title is 'Salary' the INCOME graph does not display anything, when I put it on power it works normally, but when I put it back on SALARY there is nothing (see attached image).
Is there a reason for this?

-------------------------------------------------------
Bonjour jean bonjour Derek, j'ai essayé le 'TEST_COMPTE2.zip' de derek et je n'arrive pas a afficher les revenus sur le graphique. J'ai pourtant entrée un mouvement en + mais comme l'intitulé est 'Salaire' le graphique REVENU n'affiche rien, quand je le met sur alimentation il fonctionne normalement, mais quand je le remet sur SALAIRE il n'y a plus rien (voir image jointe).
Y a t'il une raison a cela ?

Sabine

Post's attachments

Attachment icon Copie.jpg 92.45 kb, 27 downloads since 2023-06-03 

Re: Using a PieChart

Hi Sabine,
Can you attach your project?
Derek.

12 (edited by sdpc62 2023-06-04 08:46:47)

Re: Using a PieChart

Hi Derek, this project is the one you created, I was trying it out and noticed that the REVENUE chart was not appearing when I select SALARY, can you enlighten me on how to enter a move + because a positive addition does not add to the graph.
THANKS

Post's attachments

Attachment icon TEST_COMPTE2.zip 506.93 kb, 97 downloads since 2023-06-04 

Re: Using a PieChart

Hi all,
I thought it was my problem. But I don't see anything.

Post's attachments

Attachment icon Senza titolo.png 180.68 kb, 25 downloads since 2023-06-04 

Re: Using a PieChart

As it is currently set up, you do not see any data in the graphs unless you select a date range (look at line 120 and line 152 in the script).
If you want to show graphs without a selected date range (ie show ALL the data), you'll need to modify the script.
Derek.

Re: Using a PieChart

Hi derek, thank you! I missed knowing that it was necessary to choose the dates.