topic Re: AGGR and Set Analysis in QlikView

AGGR and Set Analysis

jcampbell474 — Tue, 23 Aug 2016 21:39:11 GMT

I'm trying to get the expression below to work, but it comes to zero.

Sum(Aggr(Sum({< FiscalYearMonth = {"$(=vYMOM)"}>} WP), Product))

I need the sum of WP, aggregated by Product. The variable is a date reference. Is there something wrong with my syntax?

Thanks in advance!

Re: AGGR and Set Analysis

sunny_talwar — Tue, 23 Aug 2016 21:44:27 GMT

What do you get for this?

=vYMOM in a text box object?

Also how do you create FiscalYearMonth field in the script? and how is vYMOM defined? Check this post for Dates in Set Analysis

Re: AGGR and Set Analysis

ToniKautto — Tue, 23 Aug 2016 22:52:21 GMT

For troubleshooting or development of Aggr() based expression I would suggest that you start with setting up a straight table with the same dimension and expression as you are using in the Aggr() function. This will visualize the aggregation and make it easier to understand the result from Aggr().

Dimension: Product

Expression: Sum({< FiscalYearMonth = {"$(=vYMOM)"}>} WP)

One thing to keep in mind is that the dollar expansion is done prior to the chart being calculated, so the result might not always be as you anticipated. See my related post for an example Set expression and dollar expansion‌.

A sample QVW file would make it easier to advise on the aggregation and how to get the result you expect.

Re: AGGR and Set Analysis

jcampbell474 — Tue, 23 Aug 2016 23:45:36 GMT

It depends on the year/month I have selected. Just tested it with 201605 selected. The text box displayed 201505. It's numeric.

(YMOM stands for Year, Month over Month.)

Re: AGGR and Set Analysis

sunny_talwar — Wed, 24 Aug 2016 00:52:02 GMT

So this is working only when you make a selection? Is that consistent with what you are looking for?? Have you looked into tko‌'s advice. He has mentioned a very good exercise which I use almost every single time where I create a straight table to understand the imaginary table created by the Aggr() function to check if the table created by Aggr() is correct or not.

If nothing works, I would suggest providing a sample so that we can help you better.

Best,

Sunny

Re: AGGR and Set Analysis

jcampbell474 — Wed, 24 Aug 2016 02:47:17 GMT

Now that you put it like that, AGGR might not be what I need.

Last Friday, I ran into a problem where values in a table were multiplied by 28 (4 weeks x 7 days) when a sum was applied in the expression. Zero joins, but the table consists of several concatenated tables. The data is a mix of daily and monthly records.

I apply filters and watch data in a table box to see what is going on and it only shows the values one time. I.e., Not multiplied by anything or duplicated in any way. Each value has one row/record. I had a couple of peers look at it and they too couldn't find anything wrong. So, I opened a case with QV tech support. Monday, QV recommended that I use AGGR and apply a couple of dimensions. I did and it worked...until I got to the set analysis expressions. Now, I'm feeling like AGGR is not the right solution - possibly a band aid. Feels more like something with the data model.

Do you (or anyone) have any suggestions? Is it bad practice to concatenate daily and monthly data (both share the date field YearMonth).

I know it's hard to diagnose issue like this without seeing the data. Unfortunately, I cannot share the app due to sensitive company data. Outside of sharing the application though, I'll try about anything. I really need to get it figured out.

Thank you for your help!

Btw, Toni. Thank you for your suggestion. I certainly see value in the exercise you described. Per my note above, I'm feeling more and more like AGGR isn't the solution I need. If you have any ideas, I would be grateful to hear them.

Re: AGGR and Set Analysis

ToniKautto — Wed, 24 Aug 2016 03:33:34 GMT

The set expression is equal to a manual change of selection. This means that the selection change is applied prior to the chart being calculated. If you expect to apply different selection on each row, this is not possible through set expression. the solution for such scenario is sometimes to introduce a flag field in the data model that selects the expected subset of rows, and gives the aggregation result for these rows. In other cases you might have to look at other ways to aggregate, perhaps by including a IF statement.

Understandable that you can not share corporate data. Is it possible for you to make a very simple data example in a excel sheet and explain what your expected results would be in a straight table format? Without any data, the answers you get will be quite vague and will not help you get forward very quickly.

Btw, I am not able to find a case from you in the Qlik Support system. Perhaps you get support from a Qlik Partner? Or is the case logged under different name than yours?

List box and table box behaves the same way as dimensions in a chart. They will be presented with distinct values, i.e. once. For this reason I always work with straight table objects, and use a Count() based expression to find out the re-occurrence frequency or rows. Quick way is to enable frequency in the list box, see List Box > Properties > General tab.

Re: AGGR and Set Analysis

sunny_talwar — Wed, 24 Aug 2016 03:48:53 GMT

Can this thread address your confidentiality concerns?

Preparing examples for Upload - Reduction and Data Scrambling

Re: AGGR and Set Analysis

jcampbell474 — Wed, 24 Aug 2016 11:46:28 GMT

Toni, thank you. Using a straight table to review data for duplicates is a great suggestion/solution. I always used a table box.

My case is: 00860770. It's under my manager's name. I uploaded the application Friday. Wladimir worked on it over the weekend and Monday, suggesting that I use AGGR.

Re: AGGR and Set Analysis

jcampbell474 — Wed, 24 Aug 2016 11:48:08 GMT

Sunny, I reviewed that thread. There are too many objects, etc., that cannot be scrambled. In addition, I think that my company would generally be displeased.

Thank you for the suggestion though!

Re: AGGR and Set Analysis

sunny_talwar — Wed, 24 Aug 2016 12:10:07 GMT

I see where you are coming for, but generally it is kind of difficult to help with out knowing what we are dealing with. May be not for this thread, but do read this -> QlikCommunity Tip: How to get answers to your post?‌

Best,

Sunny

Re: AGGR and Set Analysis

jcampbell474 — Wed, 24 Aug 2016 15:12:48 GMT

Toni, your advice solved my issue.

I probably should have known about a table box only showing distinct values. Kept looking everywhere for duplicate records (as did my peers and apparently QV support), but never found any. Used a straight table w/a count and bam - the dupes appeared. It just took a simple load distinct to resolve the issue.

I was really scratching my head as it didn't look 'natural' to not see duplicate records but see expressions that indicate duplication exists.

I really appreciate your and Sunny's assistance. Many, many thanks!