Aggregating across dimensions with multiple criteria

[#1]

Please see attached. In cells EA, EB and EC, the correct denominator is shown in red. the denominator should be the number of accounts at age= 0 but for only those vintages that are active at a given age.

[sunny_talwar]

So this is the correct denominator?

[#3]

Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

So this is the correct denominator?

The correct denominator is what is in row 134. the sum range moves up by 1 cell as we move towards the right in the row.

[sunny_talwar]

I think this matches your requirement but with one caveat.... This will work only in QV 12 or above or Qlik Sense

The expression I used is this

Sum(tot_active_accts)/Sum(Aggr(Above(Sum(tot_active_accts), age), (vintage_YYYYMM, (NUMERIC)), (age, (NUMERIC))))

Where the part in the red defines the sorting in the expression and is only available in QV12 or Qlik Sense.

The sortable Aggr function is finally here!‌

If you don't have either of those, you will have to sort vintage_YYYYMM and age in the script to make this expression work.

Best,

Sunny

[#5]

Hi Sunny,

Thank you so much for your continued help. We are very close. This expression works, but as soon as I select vintages older than 200507, it breaks down. The current I am using shows 1 as the denominator for an old vintage of 190209 because it has age=min(age) condition (Sum({$<age ={$(=min(age)). So even if we don't have don't have an age of 0 for a vintage, it takes the minimum age, in this case, 1180 and uses that number in the denominator. How can we incorporate that into the expression you just suggested?

Once again, thanks a lot for your help.

[#6]

stalwar1@stalwar1‌johnw‌ Was wondering if you Gurus could save my life here. John, not sure if you have been following but we are trying to create an expression that changes the range of values in the dimension across which we are aggregating as we move along the x axis. Please read above.

[#7]

stalwar1‌ is a legend sorry.

[sunny_talwar]

Are you looking for this?

I had to use binary load to be able to run the script to create a new field. Let me know if this resolves your issue or not

[johnw]

I saw the post, but saw that Sunny was on top of it, and figured you were in good hands. I also don't have QV12 yet so no sortable aggr() for me yet. I really should push to get us to upgrade soon.

[#10]

Thanks stalwar1 yes, and when you select all vintages, does it still show a declining curve? what is the binary field you created?

One final step, I want a separate curve to show for each depcat3. The app I attached only has one depcat3 as a sample, but if I load multiple categories in there, the chart doesn't show me multiple lines for each category, just like the vintages.