Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cranium144
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
sunny_talwar

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
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
Creator
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?

MarcoWedel

please post a sample application to demonstrate this behaviour.

lcontezini
Partner - Creator
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.

cranium144
Creator
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?

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

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?