Expression based on a different dimension.

[avinashelite]

Hi All,

I have a bit tricky requirement, In a bar chart we need to add one expression which should display the values based on another dimension:

eg

table1:

Issueid,month,closed

1,Jan 14,1

2,Jan 14,0

3,Dec 14,0

4,Sep 14,1

5,Sep 14,2

table2:

issueid,month2,open

1,Dec 14,1

2,Dec 14,1

3,Sep 14,1

4,Jan 14,1

My bar chart is based on the month dimension of the table1 i am counting the closed issues, it is working fine . But my requirement is for the same dimension i.e month i need to add a expression which should show the count based on the month2 as the dimension!!!

I have used if condition but its not giving proper result.

If anyone had such a requirement can you please explain how to do this

[tresesco]

Are the tables linked? (issueid(s) seem to be different )If not, can be linked with month fields.

[avinashelite]

Hi tresesco

It has been linked with issue id, when I make it as 2 different charts it work fine. my requirement is they want the dimension to be same  but the expression calculation should be based on the month2 dimension of another table

[avinashelite]

Hi tresesco , any inputs on this??

[tresesco]

It's not impossible wihtout changing the model, however, it is recommended such situations to be handled in the script itself. Please find the solution (attached) in the front-end that is really complex and might create confusion. I used synthetic dimension.

[avinashelite]

Hi tresesco

Thanks a lot for your valuable time, can you please explain how we can achieve this in script ???

[tresesco · Accepted solution]

Let the synthetic key be there with id&month or create a composite key. PFA

[avinashelite]

Thanks a lot.

one more thing how the use of concat with chr(39) make a difference, because i also tried with similar approach using pick and match but it yield in wrong result. 

[tresesco]

How did your expression and dimension look like?

[avinashelite]

actually I had fixed the dimension to month and in the expression , I had used  pick and match function to match month with month2 it was matching but the count was not correct.

What is the use of chr(39) and how to use it?? can you please explain