Adjacent value for a highest value in Webi report 4.1

[former_member334960]

Hi,

I am working on a Webi report where my requirement is to find the adjacent value for a highest value for in every week.

Given table :

Week	Date	Usage
Week1	Jan1,2014	1
Week1	Jan2,2014	108
Week1	Jan3,2014	72
Week1	Jan4,2014	3
Week1	Jan5,2014	16
Week2	Jan6,2014	135
Week2	Jan7,2014	158

Output:

Week	Variable1	Date
Week1	108	Jan2,2014
Week2	158	Jan7,2014

I have created two variables:

1) To get the maximum usage value

Variable1: max(usage) foreach (Week)

2) To get the date for a max usage value

Variable2: if((usage)=(Variable1)) then (Date)

But when I use the vatiable2 in the table, it gives me blank values.

Could you please help on this?

Thanks.

[Former Member · Accepted solution]

Hi,

Create a Measure

[MaxUsage]=Max([Usage]) In ([Week])

Create a dimension [Maxdate]=If [Usage]=[MaxUsage] Then [Date] Else "X"

Now Pull [Week], [MaxUsage] and [Maxdate] in the report and apply Block level Filter as [Maxdate] not equal to "X"

Regards

Niraj

[former_member334960 · Accepted solution]

Hi Niraj,

I have created the variables but when i pull the maxdate, I get only "X" but not any date.

Week     MaxUsage     Maxdate

Week1     108                    X

Week2     158                    X

Thanks

[Former Member · Accepted solution]

I am getting the expected results not sure why its not coming at your end try

=[Date] Where([Usage]=[MaxUsage])

On a separate Column and if you get correct result delete MaxDate from block but not Filter from block.

Regards

Niraj

[former_member334960 · Accepted solution]

Hi Niraj,

Thank you.

That worked.

[Former Member · Accepted solution]

Hi Poojitha,

1) Can you try creating below variable

maxvar=Max([usage]) ForEach ([week])

2) Then create your table with the fields week,date and maxvar

Let me know if you face any issue

[Former Member]

Hi,

Try this:

Week                Date             Usage1 (=[Usage] ForAll [Date] )

Check what results you get.

Regards,

Yuvraj

[former_member334960]

By applying Usage1=(usage) forall (date) , I get all the usage values.

I am able to get the "max usage value" for each week, but the date value for the "max usage value" is not appearing.

[Former Member]

Sorry i missed Max in the formula. it should be =Max([Usage]) ForAll [Date]