topic Re: 3 year unit sum average for the selected 'character string' fiscal year in App Development

3 year unit sum average for the selected 'character string' fiscal year: without creating additional value

nezuko_kamado — Tue, 17 Sep 2024 21:58:15 GMT

How can I draw a bar chart by quarters(qtr) of the average of the selected FY, previous FY and two previous FY?The actual data combined with multiple large data sets, and I need to keep the fiscal year format as original (character) to avoid errors. Because FY is string value, I failed to select the previous, and 2year previous FY using -1 or -2.

Instead of changing FY to numeric, first, I add the table assigning the order of fiscal year(FY). Because I need to keep FY value as it is. (I know how to convert it as numeric, but I'm looking for a way to pick previous two years with keeping it as string)

FYsort:
Load*inline [
FY, SortOrder
FY19, 1
FY20, 2
FY21, 3
FY22, 4
FY23, 5
FY24, 6
FY25, 7
]

but still not able to grab previous FYs.

The below is the sample table you can use

Table:
Load*inline [
FY, Qtr, Unit
FY19, 1, 10
FY19, 2, 30
FY19, 3, 25
FY19, 4, 40
FY20, 1, 35
FY20, 2, 25
FY20, 3, 30
FY20, 4, 50
FY21, 1, 20
FY21, 2, 15
FY21, 3, 50
FY21, 4, 45
FY22, 1, 30
FY22, 2, 25
FY22, 3, 25
FY22, 4, 50
FY23, 1, 40
FY23, 2, 25
FY23, 3, 40
FY23, 4, 10
FY24, 1, 20
FY24, 2, 43
FY24, 3, 50
FY24, 4, 10
FY25, 1, 30
FY25, 2, 45
FY25, 3, 25
FY25, 4, 50
];

Re: 3 year unit sum average for the selected 'character string' fiscal year

MatheusC — Tue, 17 Sep 2024 18:44:51 GMT

@nezuko_kamado
Try extracting the final numbers from your FY field

Num(SubField(FY, 'FY', -1)) as Finalyear.

and then in the application you can use the max() function

- Matheus

Re: 3 year unit sum average for the selected 'character string' fiscal year

nezuko_kamado — Tue, 17 Sep 2024 21:55:48 GMT

Thank you, I know this could be an option too but I'm looking for the way without creating an value.

Because my app has 10 tables with large sets, and there should be a reason this FY should stay as character. If I create a new value of Final year, there's no room for another filter for the new value.

Re: 3 year unit sum average for the selected 'character string' fiscal year: without creating additional value

Kushal_Chawda — Wed, 18 Sep 2024 09:09:43 GMT

@nezuko_kamado try something like below for max(FY)-1

=sum({<FY ={"FY$(=max(keepchar(FY,'0123456789'))-1)"}>}Unit)