Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Thank you in advance.
Qlik Sense
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!
Try this:
=Only(PRICE)/only({$<DATE={'$(=MIN(Total <PRODUCT_ID> DATE))'}>} Total <PRODUCT_ID> PRICE)
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.
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.
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))
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!