Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
yes that can help.
paste the expression of feb and jan from one of the measures
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
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)
))))
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
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
))))
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
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
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