Measure in x-axis and dimension as y-axis

[puneetagarwal]

Hi All,

Please check the table and requirement below and help me if it is possible.

Data has Month and Balances
Eg

	Jan	Feb	Mar	Apr
Op Bal	100	200	300	400
Collection	50	40	80	60
New	20	30	40	50
Cl Bal	70	190	260	390

We are able to prepare a chart with Month on X axis and Balances on Y axis.

Current chart

Requirement is to show the balances on X axis

Regards,
Puneet Agarwal

[puneetagarwal]

Correct Yoshida

I tried with the below solution i got the chart but the values are not displaying or calculating correctly.

do you need expression which i wrote for those balances, i mean will that help?

Regards,
Puneet Agarwal

[Yoshidaqlik]

yes that can help.
paste the expression of feb and jan from one of the measures

[puneetagarwal]

Hi Yoshida,

Dimension i have used is Valuelist as you have suggested.

Measure: 
Opl Bal:  rangesum(above(Sum({<MONTH,Entity>}Sales))) * Avg(1)
New: sum({<[Current Status]={'New'},Entity>}sales)
Collec: rangesum(above(Sum({<MONTH,Entity>}Sales))) * Avg(1) +
sum({<[Current Status]={'New'},Entity>}sales) -
sum({<Entity>} Sales))
Cl Bal:sum({<Entity>} Sales)

Regards,
Puneet Agarwal

[Yoshidaqlik]

for January by Example;

I think this should work, if the month dimension is a text, if not, adjust the jan value

=if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Op Bal',rangesum(above(Sum({<MONTH={'jan'},Entity>}Sales))) * Avg(1),
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'New',sum({<MONTH={'jan'},[Current Status]={'New'},Entity>}sales),
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Collection',rangesum(above(Sum({<MONTH={'jan'},Entity>}Sales))) * Avg(1) +
sum({<MONTH={'jan'},[Current Status]={'New'},Entity>}sales) -
sum({<MONTH={'jan'},Entity>} Sales),
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Cl Bal',sum({<MONTH={'jan'},Entity>} Sales)
))))

[puneetagarwal]

Hi Yoshida,

Apologies for late reply i tried the expression you have provided but unfortunately i'm not getting the correct values for particular balance and one more observation values of Op Bal and Cl Bal are appearing same and Collection and New values getting same value. 

Unable to understand where it went wrong. Any suggestions?

Regards,
Puneet Agarwal

[Yoshidaqlik]

Test That Way,

see if they give different results

=if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Op Bal',1,
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'New',2,
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Collection',3,
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Cl Bal',4
))))

[puneetagarwal]

Hi Yoshida,

The expression you gave is working fine i mean below one

=if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Op Bal',1,
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'New',2,
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Collection',3,
if(ValueList('Op Bal','Collection','New','Cl Bal') = 'Cl Bal',4
))))

It is giving the respective  1,2,3,4 but when i apply my expression i'm again getting the same value for 'Cl Bal' and 'Op bal'
and 'New' and 'Collection' same.

Regards,
Puneet Agarwal

[Yoshidaqlik]

That's weird,

should work,

Can you share the QVF file with a data sample so that I can get a better look?

Regards

Rafael Yoshida

[puneetagarwal]

Hi Yoshida,

I might not be able to upload a QVF, We are almost near but couldn't understand why we are getting same values will try from my end and update you if any.

Regards,
Puneet Agarwal