topic Re: Set Analysis Issues in QlikView

Set Analysis Issues

Wed, 25 Nov 2020 16:16:04 GMT

Hello Community,

I am fairly new to Qlikview, having only been using it for about 3 weeks. I am having a set analysis issue. I am trying to create an expression that returns a column with a running daily average based on a transaction code dimension. My code is as follows:

Sum ({

<[Day of Week]=

{"=$(=num(

weekday(

date(

date#(

vSpecifyDate, 'MM-DD-YYYY')

,'MM-DD-YYYY'))))

} Frequency)

With [Day of the Week] being a dimension referencing a weekday like Monday, Tuesday.... in a numeric or text form. vSpecifyDate is a variable that references a date value in an input box, and Frequency being the numeric dimension I am trying to sum. While the table is sorted on a transaction code dimension.

Ultimately I will use this expression to identify and highlight statistical outlier cells in a straight table. At the moment I am trying to create a column in a straight table that will return the correctly summed value for Frequency. My issue is this: the expression =num(weekday(date(date#(vSpecifyDate, 'MM-DD-YYYY'),'MM-DD-YYYY'))) successfully returns a number that corresponds with a specific day of the week. However when put it in the larger expression it will not correctly return that day of the week's average frequency value, instead it will return the entire summed frequency value. When I delete =num(weekday(date(date#(vSpecifyDate, 'MM-DD-YYYY'),'MM-DD-YYYY'))) and replace it with the same number that it returns, my larger expression will correctly sum only that day of the week.

Would any of you have any ideas why this occurs? Or how to fix it so that the set analysis works?

I have already read this entire document https://community.qlik.com/servlet/JiveServlet/download/4951-6-93291/Les%20set%20analysis_ENG.pdf , watched multiple Youtube videos but to no avail, and read other discussions on this blog. Please help me.

Re: Set Analysis Issues

Gysbert_Wassenaar — Thu, 09 Jun 2016 15:37:44 GMT

Try removing the = character directly in front of $(=num(. In other words replace {"=$(=num( with {"$(=num(:

Sum ({<[Day of Week]={$(=num(weekday(date(date#(vSpecifyDate, 'MM-DD-YYYY'),'MM-DD-YYYY'))))}>} Frequency)

Re: Set Analysis Issues

Thu, 09 Jun 2016 15:46:06 GMT

Are you......Are you a wizard? I've been playing with this function for > 8 hours now. So just to clarify you also deleted the "" as well as the = sign. Just out of curiosity, if you could would you please explain the logic behind this change?

Re: Set Analysis Issues

Gysbert_Wassenaar — Thu, 09 Jun 2016 16:00:16 GMT

Actually, I'm kinda impressed that you're messing around with set analysis expressions like that with only 3 weeks of experience. I have a couple of years more experience. That probably helped. But perhaps just coming in with a fresh mind allowed me to see it quicker.

Yeah, I removed the quotes too. Your expression returns a number so no quotes are necessary in this case. Actually the date() function inside the weekday function can be remove too.

Re: Set Analysis Issues

Thu, 09 Jun 2016 16:07:02 GMT

Thank you! I can't express how helpful you were. I only hope to someday be at your level. Cheers mate.