topic Re: Limit dimension in aggregated chart in App Development

Limit dimension in aggregated chart

Anonymous — Tue, 24 May 2016 07:54:26 GMT

Hi.

I have such a strange problem:

I have aggregated chart (rangesum above fo months from the begining of data till now) and it is ok. But I need to see only last 12 months of it.

I cannot limit dimension for top 12 (months) becouse I need calculate all months.

Screens below. I hope anybody can help.

And if I limit this chart with dimension settings or set expression I will only get calculated last 12 months which is wrong.

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 10:15:41 GMT

I am able to get the line chart for the part needed, but not able to remove the dates from axis:

May be reddys310‌ can help

UPDATE: Expression used:

RangeSum(Above(Sum({<[Year-Month]>}Value), 0, RowNo())) *

Avg({<[Year-Month] = {"$(='>=' & Date(AddMonths(Max([Year-Month]), -12), 'YYYY-MM') & '<=' & Date(AddMonths(Max([Year-Month]), 0), 'YYYY-MM'))"}>}1)

Re: Limit dimension in aggregated chart

swuehl — Tue, 24 May 2016 10:21:54 GMT

Sunny, looks quite fine just opening your QVF:

I think I am using QS 2.1.1 still...

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 10:23:15 GMT

Strange, I am using this:

Re: Limit dimension in aggregated chart

Anonymous — Tue, 24 May 2016 11:00:49 GMT

Perhaps, I am using Sense 2.2 and I see as Sunny

Re: Limit dimension in aggregated chart

reddy-s — Tue, 24 May 2016 11:18:20 GMT

Hi Sunny and Kamil,

You can use this to remove the dates from the axis:

Thanks,

Sangram.

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 11:23:16 GMT

I knew it. I knew you won't disappoint me with a Qlik Sense question sangram‌. You are my go to man when it comes to Qlik Sense . Thank you for your response.

Re: Limit dimension in aggregated chart

swuehl — Tue, 24 May 2016 11:24:45 GMT

Sunny, can you open and have a look at this sample?

As an alternative approach, the OP could use an AsOf table:

The As-Of Table

Re: Limit dimension in aggregated chart

reddy-s — Tue, 24 May 2016 11:26:00 GMT

Hi Sunny,

Thank you so much for your kind words, but frankly speaking I am getting to learn a lot from your contributions in the community and good work. I should be the one thanking you.

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 11:27:04 GMT

This one looks good

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 11:29:05 GMT

But Sangram doing that just removes the axis labels. It still is squished to the right

Why would it not show only those months where we are showing the data?

Re: Limit dimension in aggregated chart

Anonymous — Tue, 24 May 2016 11:31:01 GMT

Especially that date labels are needed

Re: Limit dimension in aggregated chart

swuehl — Tue, 24 May 2016 12:12:33 GMT

Kamil, neither of the sample QVF is working for you?

Have you considered using an AsOf Table then?

Here is another document:

Calculating rolling n-period totals, averages or other aggregations

Re: Limit dimension in aggregated chart

reddy-s — Tue, 24 May 2016 12:28:38 GMT

Hi Sunny,

In that case you can hide them, if you go to the add-ons panel in the properties of the line chart, you can uncheck the show zero values and they should be gone.

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 12:30:35 GMT

I already did that, and it is not going away for some reason

Re: Limit dimension in aggregated chart

Anonymous — Tue, 24 May 2016 12:33:18 GMT

This discussion is to fast for me. Youe second qvf is ok. Thanks. Now I need to understand it! Great thanks to You all

Re: Limit dimension in aggregated chart

reddy-s — Tue, 24 May 2016 13:03:16 GMT

Hi Sunny,

I believe its because of the table calculations which are being performed (Range sum) . There might be original values but the calculated ones are showing nulls and these are not being interpreted by the visualization.

Can we do the calculation in the data model itself?

Thanks,

Sangram.

Re: Limit dimension in aggregated chart

sunny_talwar — Tue, 24 May 2016 13:08:38 GMT

It seems to be working in one version: Re: Limit dimension in aggregated chart‌, but not in our version?

Re: Limit dimension in aggregated chart

Anonymous — Tue, 24 May 2016 13:25:41 GMT

@swuehl. I knew that your solution won't be easy for me. I'm not into set expression, so could you explain me what have you done with this calculation step by step? Especially with set expression. Don't hesitete to write here or (maybe more convenient mail klewandowski@solemis.com)

Only(

{<[Year-Month] = {"$(='>=' & Date(AddMonths(Max([Year-Month]), -12), 'YYYY-MM') & '<=' & Date(AddMonths(Max([Year-Month]), 0), 'YYYY-MM'))"}>}

Aggr( RangeSum(Above(Sum({<[Year-Month]>}Value), 0, RowNo()))

,([Year-Month], (NUMERIC, ASCENDING) )

)

Re: Limit dimension in aggregated chart

swuehl — Tue, 24 May 2016 15:21:09 GMT

Kamil,

it's well described in

Accumulative Sums

The sortable Aggr function is finally here!

If you have any further questions, just drop me a note