topic Re: MonthName Value In Set Analysis in QlikView

MonthName Value In Set Analysis

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

Hi everyone,

I'm having really tough time finding the right value to put in set analysis that would sum up a field by a month name. Attached is a sample data set and an app two text boxes - one is using sum of Count field for month of June 2018 expression and the other is using sum of Count field for date of June 7th, 2018. The box that uses Date field calculates the sum correctly, but the box that uses Month field in set analysis is not.

I'm using the following expression in Set Analysis with Month field:

=Sum({<Month = {'Jun 2018'}>} Count)

It's always returning 0. Does anyone know why that's not working and how to adjust it to have the expression return correct sum?

Thanks,

Mikhail B.

Re: MonthName Value In Set Analysis

trdandamudi — Thu, 07 Jun 2018 12:55:25 GMT

Both text boxes are showing as 1 and which is correct based on the source data. What are you expecting as a output for each of that text boxes ?

Re: MonthName Value In Set Analysis

mbespartochnyy — Thu, 07 Jun 2018 12:58:43 GMT

The top text box is showing 0 for me:

Mikhail B.

Re: MonthName Value In Set Analysis

trdandamudi — Thu, 07 Jun 2018 13:00:20 GMT

It is showing as below on my side:

Close the qlikview and reopen it and see if that works ?

Re: MonthName Value In Set Analysis

sunny_talwar — Thu, 07 Jun 2018 13:00:23 GMT

I am seeing 1

Re: MonthName Value In Set Analysis

mbespartochnyy — Thu, 07 Jun 2018 13:07:20 GMT

That's so strange. I'll try reinstalling QV. Maybe that's the issue. I'm running November 2015 SR5, version 12.20.20600.0.

I'll see if reinstalling or downgrading will help.

Mikhail B.

Re: MonthName Value In Set Analysis

mbespartochnyy — Thu, 07 Jun 2018 13:21:41 GMT

It looks like it's a QV bug. I tried uninstalling v 12.20.20600.0 and reinstalling it again and got the same results. I then installed an older version (v 12.10.20600.0) and it worked fine:

Also, here are some reference posts that add content behind the issue and the fix:

https://community.qlik.com/blogs/qlikviewdesignblog/2017/09/11/quotes-in-set-analysis

and

https://community.qlik.com/thread/268274

It looks like in v12.20.20600.0, the issue may have been fixed so using double quote instead of single quote would do the trick. Changing the expression to the one below worked for me in v12.20.20600.0:

=Sum({<Month = {"Jun 2018"}>} Count)

Credit to Sunny for helping me find the fix and for sharing HIC's blog post.

Hopefully, this will help others who also ran into this issue. It helped me wrap my mind around the difference between single and double quotes in set analysis and what was going on between the two different versions.

Mikhail B.

Re: MonthName Value In Set Analysis

trdandamudi — Thu, 07 Jun 2018 13:24:07 GMT

Good to know. I am also using version 12.10 on my side.