topic Re: Counting the no. of Days in App Development

Counting the no. of Days

hasmyt — Tue, 14 Jun 2022 06:10:36 GMT

Hello Everyone,

I have a field which contains target values corresponding to months like for example:

Month Target

Jan 100

Feb 200

I want to divide this target by no. of days to get day wise target.

For example in Feb, I want to divide 200 by 28.

How can I do it?

Re: Counting the no. of Days

justISO — Tue, 14 Jun 2022 07:44:49 GMT

Hi, it depends a bit of how your Month field are made, because if it coming from date field, you can simply take days from end of month:

sum(Target) / day(monthend([date field]))

If you only have Month name, you need incorporate conversion from month name to date first, with something like this:

MakeDate(2022, Num(Month(Date#([Month field], 'MMMM'))), 1)

and insert that as [date field] in first formula.

Re: Counting the no. of Days

hasmyt — Tue, 14 Jun 2022 09:42:35 GMT

Got it. But I have date field coming from one table and the target field coming from another, so how do I go about it?

Re: Counting the no. of Days

justISO — Tue, 14 Jun 2022 10:45:01 GMT

Are you going to calculate that in report level or in load script? Because there are, again, multiple ways. If you calculate this target-per-day in report, suggested solution should work. If you want that in load, depends on your data model structure, you can merge those tables into one and calculate that target-per-day, or you can make new 'temporal' table, with month and target, calculate target-per-day and then join to one of your main tables (and drop that temp table as not needed).

Re: Counting the no. of Days

hasmyt — Wed, 15 Jun 2022 04:54:47 GMT

Worked perfectly. Thank you.