Calculate dataset

1

Hi All,
i need help on dataset formula, i need to calculate data on the table in a week, for the exaple, we have a class schedule, and we have teacher ( example name andy and lina ) column, so i need to calculate in a week how many time teacher andy and teacher lina have a class in this week.

thank you

2

Try to use ChatGPT to formulate this for you… it should work fine.

3

ok, i will try now

thank you

4

Assumed in js you can use this:

const schedule = [
  { teacher: 'Andy', date: '2023-05-10 09:00:00' },
  { teacher: 'Lina', date: '2023-05-10 10:00:00' },
  { teacher: 'Andy', date: '2023-05-11 11:00:00' },
  { teacher: 'Lina', date: '2023-05-12 12:00:00' },
  { teacher: 'Andy', date: '2023-05-13 13:00:00' },
  { teacher: 'Lina', date: '2023-05-14 14:00:00' },
  { teacher: 'Andy', date: '2023-05-15 15:00:00' },
];

function countClassesByTimeRange(teacher, start, end) {
  return schedule.filter(({ teacher: t, date }) =>
    t === teacher && new Date(date) >= start && new Date(date) <= end
  ).length;
}

// Example usage: count how many classes "Andy" has in the last week
const oneWeekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
const today = new Date();
const countAndyLastWeek = countClassesByTimeRange('Andy', oneWeekAgo, today);

console.log(`Andy had ${countAndyLastWeek} classes in the last week.`);
5

@Schweminska and @subchan Actually, all dataset formulas need to be in SQL language… compatible with mysql.

6

@Schweminska thank you,so we create dataset and then we use dataset ticker to preview the result, but the template use js?,

@pitoco02 yes, the formula must simple, one line. i still try on chat gpt too.

7

hi i found formula to count how many schedule on this week but status when i put in xibo always invalid,
=COUNTIFS(tglmulai,“>=”&TODAY(),tglselesai,“<=”&TODAY()+7)
i use column type with formula and data type with numbe

is this formula correct?,

thank you

8

Unfortunately, we cannot determine if the formula is correct or not since no one here has access to your dataset. This is something you will have to figure out on your own.

However, here’s a tip:
Depending on the version of the CMS you are using (especially V3), these SQL formulas may have issues being accepted in the dataset forms.

To insert your formula without causing errors in the form, I suggest directly inserting the formula into the Database, in the configuration table of that dataset.

However, I want to make it clear that I cannot be held responsible for any errors this may cause in your platform. I recommend that you have some experience in manipulating a Database.

  • You need to know the ID of your dataset and, in the “datasetcolumn” table, filter the “DataSetID” column by the number of your column and insert the formula you want in the column you have already created.

image
image1428×515 52.8 KB 

SELECT * FROM datasetcolumn where DataSetID = #YOUR DATASET ID HERE#;

Obs: don’t forget to create the “formula” column in you dataset BEFORE the attempt to change it value’s…

9

thank you so much @pitoco02

let me try