Combining Peek and If(Len)

[infock12]

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

[#2]

You can create a variable, assign expression to variable through layout view or in the script.

SET vCurMonth = 'SUM(CompletedAbs)/Sum(List)';

[Anil_Babu_Samineni]

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.

[trdandamudi]

Take a look at the attached file and hope this is what you are looking for... Hope this helps....

[infock12]

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

[infock12]

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.

[infock12]

Hi all,

Did anyone had a chance to look at this please?

Thanks,

Karthik

[infock12]

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')