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.

[cranium144]

I hope this comes through okay. When selection 2018 and Jan to Apr, the particular part number has data only in April, which is what I’m seeing as a result of =Min(Date)

[sunny_talwar · Accepted solution]

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

[qlikviewwizard]

Hi,

Try this way.

='Average : '&sum(Sales)/count( {$<[Year] = {"=$(=Max([Year]))"},[YearMonth] = {"<=$(=Max([YearMonth]))"}>} distinct MonthNo )

='Count of Months : '&count( {$<[Year] = {"=$(=Max([Year]))"},[YearMonth] = {"<=$(=Max([YearMonth]))"}>} distinct MonthNo )

[cranium144]

This looks to have done it. I just added 1 since in the case of Jan to April 2018 it’s 4, not 3.

Thank you very much!

I ended up with this:

=num(sum() /

(((Year(Max({1} MonthYear))))

+1), '#,000')

[sunny_talwar]

Super, please close the thread by marking correct and helpful responses.

Best,

Sunny

[cranium144]

=num(sum([Invoice 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))))

+1), '#,000')

[cranium144]

Done.

Can you explain what the    $::Month is doing?

[sunny_talwar]

Since we are ignoring selection in all fields using 1, we need to somehow allow for selection when a Month or Year etc is selected... the syntax for that is {1<FieldName = $::FieldName>}

also, look here

What is $:: in set analysis for? | Qlik Community‌

also, some more details on the topic of set analysis

Set Analysis: syntaxes, examples