topic Re: How do I determine the number of months selected even without data? in QlikView

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

cranium144 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

sunny_talwar — Fri, 18 May 2018 17:43:20 GMT

May be this

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

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

MarcoWedel — Fri, 18 May 2018 20:39:35 GMT

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

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

cranium144 — Fri, 18 May 2018 22:25:43 GMT

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?

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

MarcoWedel — Fri, 18 May 2018 22:31:54 GMT

please post a sample application to demonstrate this behaviour.

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

lcontezini — Fri, 18 May 2018 23:07:49 GMT

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.

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

cranium144 — Fri, 18 May 2018 23:28:43 GMT

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?

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

sunny_talwar — Mon, 21 May 2018 12:29:16 GMT

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

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

cranium144 — Mon, 21 May 2018 12:39:12 GMT

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.

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

sunny_talwar — Mon, 21 May 2018 12:45:18 GMT

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?

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

cranium144 — Mon, 21 May 2018 13:00:48 GMT

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)

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

sunny_talwar — Mon, 21 May 2018 13:05:03 GMT

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

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

qlikviewwizard — Mon, 21 May 2018 13:41:17 GMT

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 )

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

cranium144 — Wed, 23 May 2018 11:06:21 GMT

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

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

sunny_talwar — Wed, 23 May 2018 11:09:12 GMT

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

Best,

Sunny

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

cranium144 — Wed, 23 May 2018 11:11:44 GMT

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

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

cranium144 — Wed, 23 May 2018 11:12:46 GMT

Done.

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

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

sunny_talwar — Wed, 23 May 2018 11:17:49 GMT

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