Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a formula like below and it worked until i needed another filter for my analysis.
sum({<[B]={1}>} [C])
The data is formed by joining tables and the simplified end result is something like this;
A (Table X) | B (Table X) | C (Table Y) | D (Table Z) |
M003-01 | 1 | 1 | C010-013 |
M003-01 | 1 | 85.9394 | C010-013 |
M003-01 | 1 | 44.3008 | C010-013 |
M003-01 | 1 | 85.9394 | C010-013 |
Q005-02 | 1 | 8.7291 | C010-013 |
Q005-03 | 1 | 10 | C010-013 |
Q005-03 | 1 | 10 | C010-013 |
M003-03 | 1 | 50.2079 | C010-015 |
M003-03 | 1 | 73.9857 | C010-015 |
M003-03 | 1 | 1 | C010-015 |
M003-03 | 1 | 73.9857 | C010-015 |
Q005-02 | 1 | 53.6805 | C010-015 |
Q005-02 | 1 | 53.6805 | C010-015 |
Q005-02 | 0 | 88.8944 | |
Q005-02 | 0 | 88.8944 | |
Q005-03 | 0 | 88.8944 | |
M003-01 | 0 | 18.0658 | |
M003-03 | 0 | 21.4971 | |
M003-03 | 0 | 21.4971 |
What i am trying, and failing, to do is getting the sum of all C while B = 1 and any A row when D is selected.
IF "C010-013" at D is selected then the rows to calculate would be;
A | B | C | D |
M003-01 | 1 | 1 | C010-013 |
M003-01 | 1 | 85.9394 | C010-013 |
M003-01 | 1 | 44.3008 | C010-013 |
M003-01 | 1 | 85.9394 | C010-013 |
Q005-02 | 1 | 8.7291 | C010-013 |
Q005-03 | 1 | 10 | C010-013 |
Q005-03 | 1 | 10 | C010-013 |
Q005-02 | 1 | 53.6805 | C010-015 |
Q005-02 | 1 | 53.6805 | C010-015 |
Q005-02 | 1 | 88.8944 | |
Q005-03 | 1 | 88.8944 | |
M003-01 | 1 | 18.0658 |
And the sum result would be;
A | B | C | D |
M003-01 | 1 | 235.2454 | C010-013 |
Q005-02 | 1 | 204.9845 | C010-013 |
Q005-03 | 1 | 108.8944 | C010-013 |
It would be best if there is a way to get this result without changing the LOAD script for the data table.
Anyone? 😱
Try to solve this using P() in your set. Something like this.
sum({<[B]={"1"}, [A] =P({<[D] ='$(=only([D] )) '>} ) >} [C])
Hello,
Thanks for the reply but unfortunately i am getting "The selection generated no data for this chart" whether i select any D or not.