topic Re: Aggregating across dimensions with multiple criteria in QlikView

Aggregating across dimensions with multiple criteria

Mon, 04 Jul 2016 20:03:49 GMT

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.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Mon, 04 Jul 2016 21:06:55 GMT

So this is the correct denominator?

Re: Aggregating across dimensions with multiple criteria

Mon, 04 Jul 2016 21:23:53 GMT

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.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Tue, 05 Jul 2016 01:11:13 GMT

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

Re: Aggregating across dimensions with multiple criteria

Tue, 05 Jul 2016 22:02:03 GMT

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.

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 16:07:48 GMT

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.

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 16:08:26 GMT

stalwar1‌ is a legend sorry.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Thu, 07 Jul 2016 16:23:54 GMT

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

Re: Aggregating across dimensions with multiple criteria

johnw — Thu, 07 Jul 2016 16:52:39 GMT

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.

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 16:58:14 GMT

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.

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 16:58:49 GMT

Thanks John. stalwar1‌ is the man!

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Thu, 07 Jul 2016 17:14:23 GMT

If nothing is selected, this is what I see.

Is this right?

Would you be able to add another category so that we can work on that?

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 17:24:40 GMT

can't tell. Can you try selecting only vintages from 2001 onwards. That should give a declining curve, and then a spike and then again a consistent decline.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Thu, 07 Jul 2016 17:28:23 GMT

Here you go

I did attach the sample for your review. Attaching it again

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 17:40:06 GMT

stalwar1‌ can you see this file with added category? I can't tell whether the chart is showing the right values until I test it in SAS. Doing it now.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Thu, 07 Jul 2016 18:44:18 GMT

Can you repost the above document after adding the new field I proposed?

LOAD date(date#(file_date, 'DDMMMYYYY'),'MMM-YYYY') as file_Date,

AutoNumber(age, vintage_YYYYMM)-1 as Sort,

filedate_YYYYMM,

age,

vintage_YYYYMM,

tot_active_accts,

ECR_flag

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where depcat3='IBC_PE';

Re: Aggregating across dimensions with multiple criteria

Thu, 07 Jul 2016 23:01:51 GMT

stalwar1‌ Please see attached. Thanks for all your help.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Fri, 08 Jul 2016 16:43:08 GMT

May be this?

Sum(tot_active_accts)/Sum(Aggr(Above(Sum(tot_active_accts), Sort), (depcat3, (TEXT)), (vintage_YYYYMM, (NUMERIC)), (age, (NUMERIC))))

Re: Aggregating across dimensions with multiple criteria

Fri, 08 Jul 2016 17:03:36 GMT

Almooostt! Why is it not showing anything now when I select just one vintage, like the chart below it which has vintage in the dimension?

Can't thank you enough.

Re: Aggregating across dimensions with multiple criteria

sunny_talwar — Fri, 08 Jul 2016 17:17:44 GMT

I am seeing this when I select 191001