Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a pivot table that has the below expression.
SUM(CompletedAbs)/Sum(List). I have also added another expression to RAG rate it.
=If(Len(Median) =0, 'Blank',
IF(Len(Median)> 0 And sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png') )
'Median' in the above is basically a target. As you can see, the formula above says if the Median is not there, then say Blank otherwise do the above calculation.
I am wondering if there is a way to say, =If(Len(Median) =0, then keep the previous month's value as a Median. To clarify, previous month's value is the outcome of the original expression Sum(CompletedAbs/Sum(List). Does Peek Function help here as I wanted it to start from Month 2 so that it can keep Month 1's value as a base?
Basically, I wanted to say,
IF(Len(Median)> 0 And sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png') ) but If =If(Len(Median) =0, and if the current month's (Sum(CompletedAbs/Sum(List) is more than the previous month's (Sum(CompletedAbs/Sum(List), 'Green', Else 'Red')
Sorry for the ignorance and I hope it is not too confusing. Any help is appreciated. I have attached the qvw and the excel sheet.
Thanks,
Karthik
You can create a variable, assign expression to variable through layout view or in the script.
SET vCurMonth = 'SUM(CompletedAbs)/Sum(List)';
Try These Two
1)
=If(Len(Median) =0, Before(SUM(CompletedAbs)/Sum(List)), IF(Len(Median)> 0 And sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png') )
2)
=If(Len(Median) =0, 'qmem://<bundled>/BuiltIn/arrow_n_y.png', IF(Len(Median)> 0 And sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png') )
Or Else, You may can explain the BRD and Share expected output.
Take a look at the attached file and hope this is what you are looking for... Hope this helps....
Hi AB and TD,
Thanks for your reply and both are helpful. I just made a slight change to your script AB because the output I require is the below. It works when I execute it individually, however, I wanted to combine the both. I tried it with AND, Comma, etc., but for some reason it doesn't work. I am sure it must be simple and elementary but somehow I am missing it. Could you shed some light on this please?
=IF(Len(Median)> 0 And sum(CompletedAbs)/Sum(List)<=Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_g.png')
=If(Len(Median) =0 And Before(SUM(CompletedAbs)/Sum(List))<=SUM(CompletedAbs)/Sum(List), 'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')
Thanks
Hi Lori,
I tried this (below) but it still does not work.
=If(Len(Median)>0 And vCurrMonth<Median, 'Green', 'Red'),
If(Len(Median)=0 And Before(vCurrMonth)<vCurrMonth, 'Green', 'Red')
It looks like a simple comma or a bracket that needs to be changed somewhere but can't figure it out unfortunately.
Hi all,
Did anyone had a chance to look at this please?
Thanks,
Karthik
Hi all,
I figured it out. It was quite simple. This worked for me. Thanks for all your time.
=IF(Len(Median)> 0 And sum(CompletedAbs)/Sum(List)<=Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', If(Len(Median) =0 And Before(SUM(CompletedAbs)/Sum(List))<=SUM(CompletedAbs)/Sum(List), 'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_s_r.png')