Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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
Highlighted

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

View solution in original post

17 Replies
Highlighted

May be this

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

Highlighted

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

Highlighted
Creator
Creator

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?

Highlighted

please post a sample application to demonstrate this behaviour.

Highlighted
Partner
Partner

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.

Highlighted
Creator
Creator

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?

Highlighted

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

Highlighted
Creator
Creator

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.

Highlighted

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?