How do I determine the number of months selected even without data?

[cranium144]

I'm looking to do a qty/Month average on some seldomly sold parts, and calculate on the number of months (or MonthYears) selected.  That's easy if it is sold every month.  But what if I select 6 months, and it only sold in 2 of those months?

As an example, I sell 600 parts over a 6 month time span, but only 2 months have data.  If I do a

sum(qty) / count(DISTINCT MonthYear) I get 600 / 2 = 300.  When in fact I want to see 600 / 6 = 100.

[sunny_talwar]

May be this

Sum(qty)/((Year(Max(MonthYear))*12 + Month(Max(MonthYear))) - (Year(Min(MonthYear))*12 + Month(Min(MonthYear))))

[MarcoWedel]

Can you post a sample application that shows the behaviour of selecting 6 YearMonth values not leading to Count(Distinct YearMonth) equalling 6?

Did you maybe select in other fields also?

In this case you might use something like

Count({1<MonthYear=$::MonthYear>} DISTINCT MonthYear)

instead.

hope this helps

regards

Marco

[cranium144]

In the case of the current year, it’s dividing by 12 instead of 5 (or whatever time selection is chosen). In the case of years with full data (previous years) it works just fine. But what about basing in on the selected time frame?

[MarcoWedel]

please post a sample application to demonstrate this behaviour.

[lcontezini]

Why don't you create a flag for the months that have data?

For example, in months that you have data, flag = 1, otherwise flag = 0. Then if you divide it by the sum(flag), you'll have only months with data in the denominator.

[cranium144]

I want to include months where there is and is not data. So I believe I need to pull out the first date of my selection and the last. Then do a date comparison. How do I do that?

[sunny_talwar]

Did you get a chance to try this?

Sum(qty)/((Year(Max(MonthYear))*12 + Month(Max(MonthYear))) - (Year(Min(MonthYear))*12 + Month(Min(MonthYear))))

The reason I ask is because you responded to both other posters and not me. This might have totally not worked... but just want to make sure that you at least gave it a try

[cranium144]

I did try this. But it appears to still only be paying attention to the months with data no matter the selection.

I guess it could be asked this way: How do I tell the earliest date in my selection? Not the earliest date in the data.

[sunny_talwar]

Min() function should give you the minimum among your selection... is this not what you are seeing? Can you share an image to show what you getting?