# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Creator

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

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.

1 Solution

Accepted Solutions
MVP

Then ignore selection in Catalog Number field like this

Sum(qty)/((Year(Max({<[Catalog Number]>} MonthYear))*12 + Month(Max({<[Catalog Number]>} MonthYear))) - (Year(Min({<[Catalog Number]>} MonthYear))*12 + Month(Min({<[Catalog Number]>} MonthYear))))

or may be this to allow for selection in Month and Year

Sum(qty)/((Year(Max({1<Month = \$::Month, Year = \$::Year, MonthYear = \$::MonthYear, Date = \$::Date>}MonthYear))*12 + Month(Max({1<Month = \$::Month, Year = \$::Year, MonthYear = \$::MonthYear, Date = \$::Date>} MonthYear))) - (Year(Min({1<Month = \$::Month, Year = \$::Year, MonthYear = \$::MonthYear, Date = \$::Date>} MonthYear))*12 + Month(Min({1<Month = \$::Month, Year = \$::Year, MonthYear = \$::MonthYear, Date = \$::Date>} MonthYear))))

17 Replies
MVP

May be this

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

MVP

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

hope this helps

regards

Marco

Creator
Author

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?

MVP

please post a sample application to demonstrate this behaviour.

Partner - Creator

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.

Creator
Author

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?

MVP

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

Creator
Author

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.

MVP

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?

Community Browser