topic Re: How to apply RangeSum in a Synthetic Dimension? in App Development

How to apply RangeSum in a Synthetic Dimension?

Anonymous — Thu, 28 Jun 2018 09:54:56 GMT

What am I doing wrong?

I am using RangeSum in a Synthetic Dimension but the result isn't the same as using RangeSum in a normal Measure Expression.

The Expression I am using in the Synthetic Dimension is:

If(ValueList('Measure Expression','Dummy')='Measure Expression', RangeSum(above(total Sum([Monthly budget]),0,RowNo(total))))

I would like to have the result as with the RangeSum in the normal Measure Expression:

RangeSum(above(total Sum([Monthly budget]),0,RowNo(total)))

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Thu, 28 Jun 2018 16:33:17 GMT

Do you have two dimensions in your chart? ValueList and FiscalMonth?

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Fri, 29 Jun 2018 07:05:51 GMT

Yes, I have two dimensions in my chart; the first dimension is the Fiscal Month and the second dimension is the ValueList.

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Fri, 29 Jun 2018 14:31:28 GMT

What is the role of ValueList()? Can you replace ValueList with an Island Table dimension which is created in the script?

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Fri, 29 Jun 2018 16:05:23 GMT

I am using the ValueList since it gives me the possibility to color trend lines in a line graph.

The following discussion gave me the idea to use Synthetic Dimensions for this purpose: Colored conditional line-charts in dimensions with high number of categories.

I am not familiar with Island Table dimensions so I don't know if that is an alternative.

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Fri, 29 Jun 2018 17:56:18 GMT

I think it should work the same way

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Mon, 02 Jul 2018 15:22:42 GMT

Dear Sunny,

Thanks for your replies!

I think the examples from the discussion I have mentioned are working correctly since they aren't using

"RangeSum(above(total Sum([Monthly budget]),0,RowNo(total))"

in their measure calculation.

In my case it seems that the use of 'total' makes it to sum over the both dimensions: first over the Fiscal Month dimension and a second time over the ValueList dimension.

Does that make sense?

And if it does, can it changed to only sum only once?

By the way: the source data is shown to the right of the line charts on the screenshot.

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Wed, 04 Jul 2018 23:14:24 GMT

You can try this without total... but the order of dimensions matter... so if this doesn't work.. then you will have to use island field with Aggr() function in your expression

RangeSum(above(Sum([Monthly budget]), 0, RowNo())

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Wed, 11 Jul 2018 06:47:09 GMT

Dear Sunny,

Thank you for the feedback.

I will have to try out the Island field with Aggr() function since the expression without total gives the monthly value, not the accumulated value.

If you have any suggestions for study material for the Island field with Aggr() function, I am very interested.

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Wed, 11 Jul 2018 10:22:33 GMT

I will see if I can find any example... but if you are able to provide some mocked up data (dummy) I can show how it can be done with your data....

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Wed, 11 Jul 2018 10:23:34 GMT

Here is an example on how Data Island can replace ValueList()

Referring from aggr() to ValueList() value

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Tue, 31 Jul 2018 07:09:09 GMT

Dear Sunny,

Thanks very much for your support.

I would like to insert the qvf file of the app that I have used in the original question of this discussion so you "can show how it can be done with" my data, but I can't see how I can insert the qvf file. It seems only Pictures, Movies and Links can be inserted.

Best regards,

Pim

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Tue, 31 Jul 2018 11:15:57 GMT

Check here

Uploading a Sample

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Tue, 31 Jul 2018 12:06:18 GMT

Hello Sunny,

I still couldn’t find the “Use advanced editor” option so I am responding ‘directly on the post’ in this way.

I have attached the qvf file that I used for my original question.

Best regards / Met vriendelijke groet,

Pim Dorresteijn

Yokogawa Europe B.V. is registered in the commercial register of the chamber of commerce under file number 31024364.

DISCLAIMER

The content of this email, including any attachments, is confidential and may be privileged. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and you are kindly requested to notify the sender immediately and delete this message. Yokogawa is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt.

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Tue, 31 Jul 2018 12:15:19 GMT

Script

LOAD

"Fiscal Month",

"Monthly budget"

FROM [lib://Data Sources/Synthetic Dimension with RangeSum.xlsx]

(ooxml, embedded labels, table is [Simple source data]);

Dim:

LOAD * Inline [

Dim

Measure Expression

Dummy

];

Chart Dimensions

Fiscal Month

Dim

Expression

Aggr(If(Dim = 'Measure Expression', RangeSum(Above(Sum([Monthly budget]), 0, RowNo()))), Dim, [Fiscal Month])

Re: How to apply RangeSum in a Synthetic Dimension?

Anonymous — Thu, 09 Aug 2018 14:47:01 GMT

Dear Sunny,

Your solution indeed gives the desired result on this test/try out/development app.

However the line graph that I finally need shall have calendar dates with continuous scale as dimension

I now noticed that in this case there isn't an option to define a second dimension.

So in de end neither an synthetic dimension nor a data island can be used......

Thanks a lot for your support.

I appreciate it very much!

Re: How to apply RangeSum in a Synthetic Dimension?

sunny_talwar — Thu, 09 Aug 2018 17:34:58 GMT

Would you be able to implement that in your sample file to show what you mean? You might be right, but I am just interested in seeing what you have.

Thanks,

Sunny