topic Re: Set analysis reference to each table's dimension or PICK/IF? in App Development

Set analysis reference to each table's dimension or PICK/IF?

antonopn — Wed, 15 Mar 2023 11:36:37 GMT

Hi everyone,

Still writing here, though ChatGPT is already solving many of the problems. But here also looking to your perspectives, not only get some useful code.

I have some batches, manufactured yearly and I need to see their cost per year. So I need to see the "batch-2015" cost for 2015,2016 etc. Four years ahead. I can do it with pivot as below, but I was trying to find a more elegant/flexible solution.

See below.

I tried with Set analysis, but I think it is not possible. For example

sum({<Year = {$(=right(BatchName,4)))} >} Cost)

Any idea how should I approach this? Should I do it in pivot, or straight table with conditional logic (PICK/IF)?

Thanks in afvance

Re: Set analysis reference to each table's dimension or PICK/IF?

marcus_sommer — Wed, 15 Mar 2023 12:26:10 GMT

I think it shouldn't be solved within the UI else within the data-model. For example with something like this:

...
CostYear - BatchStartYear + 1 as #Year
...

which just calculates the offset between the years.

Re: Set analysis reference to each table's dimension or PICK/IF?

antonopn — Wed, 15 Mar 2023 15:25:40 GMT

Thank you for the reply. I will try it