topic Re: Expression based on a different dimension. in QlikView

Expression based on a different dimension.

avinashelite — Tue, 10 Feb 2015 12:46:38 GMT

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:

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

Re: Expression based on a different dimension.

tresB — Tue, 10 Feb 2015 12:53:39 GMT

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

Re: Expression based on a different dimension.

avinashelite — Tue, 10 Feb 2015 12:56:27 GMT

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

Re: Expression based on a different dimension.

avinashelite — Tue, 10 Feb 2015 13:22:12 GMT

Hi tresesco , any inputs on this??

Re: Expression based on a different dimension.

tresB — Wed, 11 Feb 2015 05:11:10 GMT

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.

Re: Expression based on a different dimension.

avinashelite — Wed, 11 Feb 2015 05:41:09 GMT

Hi tresesco

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

Re: Expression based on a different dimension.

tresB — Wed, 11 Feb 2015 06:22:27 GMT

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

Re: Expression based on a different dimension.

avinashelite — Wed, 11 Feb 2015 06:33:00 GMT

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.

Re: Expression based on a different dimension.

tresB — Wed, 11 Feb 2015 06:57:13 GMT

How did your expression and dimension look like?

Re: Expression based on a different dimension.

avinashelite — Wed, 11 Feb 2015 07:09:06 GMT

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

Re: Expression based on a different dimension.

tresB — Wed, 11 Feb 2015 07:19:25 GMT

Chr(39) is used to create the proper input to valuelist(). And this valuelist() is more important here to understand. It is used to generate synthetic dimension(allows to go beyond the association created in the model). Learn about it, you would love it.

Re: Expression based on a different dimension.

avinashelite — Wed, 11 Feb 2015 07:33:21 GMT

thanks a lot tresesco seems to be interesting , i will research more on that.