Nested AGGR() and IF()

[PIous_314]

Hello!

I need to count the number of customers that have made at least one purchase in each of the last three weeks and have that in a chart where Week is the dimension.  I have the following expression, but it is not calculating the correct number and I cannot figure out why.  I was wondering if I needed to switch the order of IF() and AGGR(), or if there was a better way to get this result.

Thank you!

Count(distinct aggr(
    if(
        Count({< [Invoice Date] = {">=$(=date(Max(%DateKey)-20,'MM/DD/YYYY'))<=$(=date(Max(%DateKey)-14,'MM/DD/YYYY'))"} >} DISTINCT [Order Number]) >= 1 and
        Count({< [Invoice Date] = {">=$(=date(Max(%DateKey)-13,'MM/DD/YYYY'))<=$(=date(Max(%DateKey)-7,'MM/DD/YYYY'))"} >} DISTINCT [Order Number]) >= 1 and
        Count({< [Invoice Date] = {">=$(=date(Max(%DateKey)-6,'MM/DD/YYYY'))<=$(=date(Max(%DateKey),'MM/DD/YYYY'))"} >} DISTINCT [Order Number]) >= 1,
        [Customer Number]
    ),
    [Customer Number]
))

[Oleg_Troyansky]

Hi there,

If I understand the problem correctly, I see two main issues:

1. You are in violation of what I called "The Third Law of AGGR" in my book - in order to use AGGR() safely in a chart with certain chart dimensions, the dimensions of AGGR should be at least as granular (or more granular) as the chart dimensions. In your case, the chart dimension is Week, but the AGGR dimension is the Customer Number, with no week. That wouldn't work safely. To solve this issue, add Week as the second dimension in the AGGR function.

2. The second issue may or may not be a problem, depending on your needs. The Set Analysis conditions are based on the Dates, and the calculations compare the Invoice Date to Max(Date) in various ways. As you probably know, Set Analysis conditions are verified globally and not at every cell of your chart. In other words, your conditions for the last 3 weeks will always apply to the last three weeks from the Max(Date) - not for the previous 3 weeks for each week in the chart. So, if your requirement is to count customers that purchased something in each of the last 3 weeks, then the formula should work. If you wanted to show the same, but for each of the weeks in the chart, then you can't use Set Analysis that is based on Dates. You may need to look into a solution called "The As of Date Table", to facilitate this logic. Look it up. It's described in many blogs, including mine:

https://www.naturalsynergies.com/q-tip-4-how-to-use-as-of-date-table/

Allow me to invite you to the upcoming session of the Masters Summit for Qlik, where I teach advanced development techniques such as Set Analysis, AGGR, and many more.

[PIous_314]

Thank you for the response!

So, I originally tried to add Week as the secondary dimension for the AGGR() function, but then it evaluates to zero everywhere (I also tried the Date fields with the same result).

For your second point, when I add the max(%DateKey) fields as their own measure to the table, it gives me the correct dates that correspond to each week so it seems like the set analysis should be applying the correct comparisons to Invoice Date to check that there has been an order in the last three weeks for each week in the chart dimension, right?  I've added the table below for more context in case I'm not explaining myself clearly (the third column from the right is the result of the expression in question here, but the numbers are wrong.  For instance, Week 16 should be 23 and Week 15 should be 52).

[marcus_sommer]

I wouldn't use dates if I want to show and to compare against weeks. Also I would tend to avoid the aggr() and trying something like:

count(Customer) *
-(count(
   { < InvoiceWeek = {">=$(=max(DateWeek)-3)<=$(=max(DateWeek))"} >}
distinct Customer&InvoiceWeek)=3)

[PIous_314]

Could you elaborate on this solution?  I am not following exactly what you are proposing, and not sure what you mean when you say not to use dates when wanting to show and compare against weeks?

Thank you!

[marcus_sommer]

It means to use week-fields created in all used calendars - and then the distinct count of Customer & Week is a sufficient condition.

[Oleg_Troyansky]

Hi,

Based on your explanation, you want to count those customers that purchased in each one of the last 3 weeks, for each week in the table. In this case, set analysis won't help you. Set Analysis is evaluated globally, and therefore it cannot be sensitive to your Dimensions (such as Week).

You need to use the dreaded IF() function:

count( distinct IF( conditions , Customer))

and the conditions should also use IF() functions instead of Set Analysis. Depending on the complexity of the conditions, you may need to use an AGGR function with both Customer and Week as dimensions.

Cheers,

[PIous_314]

Hello,

I am a little confused - in my original post I DID use IF() conditions and also AGGR() over Customer (I also explained that when I added Week as a second AGGR dimension it evaluates to zero).

However, my expression is not returning the correct answer and I am asking if anyone can see what is wrong with my expression or if there is a different approach.  It seems like you are suggesting the solution I already tried.

Apologies if I am missing something!

[PIous_314]

Hi,

I am still not following this expression, it doesn't make any sense to me:

count(Customer) *
-(count(
   { < InvoiceWeek = {">=$(=max(DateWeek)-3)<=$(=max(DateWeek))"} >}
distinct Customer&InvoiceWeek)=3)

Can you explain exactly what is happening here in each part?  I don't see how this gets the result I am looking for.

Apologies if I am missing anything!

[marcus_sommer]

The first part counts all customers in regard to the selection state and object-dimensionalities (I think it missed a DISTINCT in my suggestion). The second part is a multiplying against a 0/1 boolean result which evaluates the condition and logically the same as: if(condition = true(), 1, 0).

And your mentioned condition was to have at least one invoice in specified 3 weeks period which is fetched with a distinct counting of customers and invoice-weeks. In many scenarios (data-model + view-requirements) will such an approach be working and being a simpler and more performant alternatively to an aggr().