Counting the no. of Days

[hasmyt]

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? 

[justISO · Accepted solution]

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.

[hasmyt]

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?

[justISO · Accepted solution]

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).

[hasmyt]

Worked perfectly. Thank you.