topic Re: Line Chart showing percent variance relative to the minimum date in the current user selection in App Development

Line Chart showing percent variance relative to the minimum date in the current user selection

chrequena_88 — Sat, 16 Nov 2024 00:55:51 GMT

Hi everyone,
I need to create a line chart that shows the percent variance of a multiple products' price over time. As the price of the products might differ from each other quite a lot, the comparison must be done in respect to the its own price at the minimum date possible according the user selection.

A sample data look like:

PRODUCT_ID	DATE	PRICE
1	1.1.2020	10
2	1.1.2020	150
3	1.1.2020	300
1	2.1.2020	12
2	2.1.2020	120
3	2.1.2020	320
1	3.1.2020	15
2	3.1.2020	110
3	3.1.2020	330
1	4.1.2020	19
2	4.1.2020	100
3	4.1.2020	350
1	5.1.2020	25
2	5.1.2020	95
3	5.1.2020	400

The percent variance would look like this in a straight table:

PRODUCT_ID	DATE	PRICE	Percent Variance
1	1.1.2020	10	100 %
1	2.1.2020	12	120 %
1	3.1.2020	15	150 %
1	4.1.2020	19	190 %
1	5.1.2020	25	250 %
2	1.1.2020	150	100 %
2	2.1.2020	120	80 %
2	3.1.2020	110	73 %
2	4.1.2020	100	67 %
2	5.1.2020	95	63 %
3	1.1.2020	300	100 %
3	2.1.2020	320	107 %
3	3.1.2020	330	110 %
3	4.1.2020	350	117 %
3	5.1.2020	400	133 %

I am having issues when creating the right expression in the line chart measure. The current (AND NOT WORKING) expression in my line chart is the following one:
- PRICE / Only({$<DATE={'$(=Min(DATE))'}>} PRICE)

The line chart dimensions are currently configure as following:
- Group: date
- Line: product_id

The expected output would be:

Price Evolution Over time respect to initial price

Thank you in advance.

Re: Line Chart showing percent variance relative to the minimum date in the current user selection

GaryGiles — Sat, 19 Dec 2020 20:56:00 GMT

Try this:

=Only(PRICE)/only({$<DATE={'$(=MIN(Total <PRODUCT_ID> DATE))'}>} Total <PRODUCT_ID> PRICE)

Re: Line Chart showing percent variance relative to the minimum date in the current user selection

chrequena_88 — Mon, 21 Dec 2020 09:05:57 GMT

Hi Gary,
Thanks for your piece of advice. But unfortunately, that didn't work.

Might I ask you what was your idea of including the keyword "TOTAL" and the modifier <PRODUCT_ID>?I think that I don't need to include a modifier for product (<PRODUCT_ID>)as I need that the products showed in the chart change dynamically according user selections.

Re: Line Chart showing percent variance relative to the minimum date in the current user selection

chrequena_88 — Mon, 21 Dec 2020 09:32:15 GMT

Now the following expression:

Only(PRICE)/only({$<DATE={'$(=MIN( DATE))'}>} Total PRICE)

works as intended partially. The line chart shows the product' price in percentage relative to its initial price, but some reason the line chart only works when only one fund is selected. That is, when more than one product is selected, the line chart doesn't show any values.

Re: Line Chart showing percent variance relative to the minimum date in the current user selection

GaryGiles — Mon, 21 Dec 2020 11:36:40 GMT

The "Total <PRODUCTI_ID>" ensures that min date is specific for each product. Give this expression a try. It work for me with your limited data set.

Only(PRICE)/SUM(Total <PRODUCT_ID> IF(DATE=AGGR(MIN(DATE),PRODUCT_ID),PRICE))

Re: Line Chart showing percent variance relative to the minimum date in the current user selection

chrequena_88 — Wed, 23 Dec 2020 09:44:03 GMT

Hi Gary,
Thank you for your response. Honestly, your last suggestion didn't work for my real dataset. I believe that the reason for this is that I have simplified the real case incorrectly, and therefore your code works for the simplified case but not for the real one.

In any case, your explanation about the use of "Total <PRODUCTI_ID>" did help me. Hence, I have modified my initial code as follows:

- PRICE / Only({$<DATE={'$(=Min(DATE))'}>} Total <PRODUCTI_ID> PRICE)

And the above code works exactly as expected.

Once again, thank you for your help!