topic Re: Simulating Multiple Rows when Only 1 Row in App Development

Simulating Multiple Rows when Only 1 Row

bc5891 — Thu, 06 Jul 2023 10:06:13 GMT

Good morning,

I have a table similar to below:

ID	OPEN DATE	CLOSE DATE
123	2016	2019

In the front end I want to show this ID across multiple years and provide it different statuses. There could also be multiple statuses per year.

Essentially in the front end I want to somehow create the following :
It would be in a bar chart ideally.
Rules:

Open in current year = New
Open in current year and not closed in that year = Ongoing
Not opened in current year and not closed in current year = Ongoing
Closed in Current Year = Closed

It would give the output like below (in an on the fly table).

ID	YEAR	STATUS
123	2016	NEW
123	2016	ONGOING
123	2017	ONGOING
123	2018	ONGOING
123	2019	CLOSED

Without doing this in the script is it possible to do this in an expression? could rangesum be used?
The reason for not doing it in the back end script is that this could massively inflate the data set if we go down to month level over time.

Thanks

Re: Simulating Multiple Rows when Only 1 Row

Or — Thu, 06 Jul 2023 10:35:42 GMT

If it's just the one value, you could do this with ValueList() to create a synthetic dimension. However, if you have multiple IDs and each has different years, I'm not sure if this'd work (it might - you'd have to write a measure that returns null of the ValueList() result isn't in the range, and then hide zero/null).

This shouldn't have too much of an impact on the app size if you do it in script, most likely, and that's easy to handle using While.

Re: Simulating Multiple Rows when Only 1 Row

bc5891 — Thu, 06 Jul 2023 11:00:16 GMT

Thanks, there are many Ids.

Do you have a simple example of a while loop referencing the ID here?

Re: Simulating Multiple Rows when Only 1 Row

Or — Thu, 06 Jul 2023 12:03:30 GMT

Load ID, OPENDATE + IterNo() - 1 as Year, If(Something=SomethingElse,'Status1','Status2') as Status

From YourTable

While OPENDATE + IterNo() - 1 <= CLOSEDATE;

Re: Simulating Multiple Rows when Only 1 Row

bc5891 — Thu, 06 Jul 2023 12:52:18 GMT

thank you very much

Re: Simulating Multiple Rows when Only 1 Row

bc5891 — Thu, 06 Jul 2023 14:59:09 GMT

1 more question if I may - I've been playing about with this and I have a similar request using Quarter but obviously the iterno() will not work in this case. can the same/similar method be used?

ID	QTRSTART	QTREND	ID	QTRSTART	QTREND	QTR
123	20211	20222	123	20211	20222	20211
			123	20211	20222	20212
			123	20211	20222	20213
			123	20211	20222	20214
			123	20211	20222	20221
			123	20211	20222	20222

Re: Simulating Multiple Rows when Only 1 Row

Or — Thu, 06 Jul 2023 16:02:28 GMT

IterNo() will still work, you'll just need to use a different formula to generate your quarters. Something like AddMonths(Start,(IterNo-1)*3 would generate quarters from a starting quarter, assuming the starting quarter is a date.