Help with Expression

[priya945]

Hi ALL,

	Region	India				UK
Metric	WeekNO	W1	W2	W3	Avg of 3Weeks	W1	W2	W3	Avg of 3Weeks
Sales		1	2	3	2	4	5	6	5
Margin		3	3	3	3	4	4	4	4

I have above data. Now for Sales Metric Average is calculated in UI but for Margin Average of 3 Weeks is calculated at DB level and I have the Field "ABC" .

Qtion: How to show the  "Avg of 3Weeks" for Margin and Sales in same table

Please suggest.

[raman_rastogi]

Try Something like this

ex-Avg({<Date={">=$(=Num(WeekStart(Max(Date),-3)))<=$(=Max(Date))"}>} Sales)

ex- Avg({<Date={">=$(=Num(WeekStart(Max(Date),-3)))<=$(=Max(Date))"}>} Margin )

Regards

Raman

[priya945]

Hi raman,

Thanks for reply but just want to know how this works in row wise average calculation for each dimension values

As I said I have one field ("ABC") calculated average in DB for Margin.

qtion is how to use DB level calculated and UI average expression in same table

Please help me if you know how to do it.

Thank you

[raman_rastogi]

Hi

If you have same level of Data details for margin in your DB as You want to shown in your pivot then you can use above formula.

[priya945]

As said I have the average calculated field ABC in dB and requirement is to use the same field for Margin "Avg of 3 weeks"

But for sales I need to calculate in UI.

Now please tell me the expressions for Margin using ABC field

As sales has Sum(sales) /3 as expression for average

Thankd

[priya945]

HI ALL

Any suggestions please

[shwetagupta]

Hi Priya,

You can.create an inline table containing

Sales

Margin

So that you can use them as dimension in pivot. (

If this does not work then only KPI box can help 

Note: Qlik plots value corresponding to any dimension in a column I.e in Table. Someone please correct if I have stated anything wrong.

[priya945]

Hi Sweta,

As said, Sales and Margin are the two Matric(Field) values which contains weekly values as shown in above pic.

For Sales, I am calculating 'Avg of 3Weeks' as Sum(values) /3 in UI but for Margin - 'Avg of 3Weeks' is coming from a field called ABC which is already calculated in DB.

if(Metric=Sales,Sum(Values)/3,only('ABC'))

or

if(Metric=Sales,Sum(Values)/3,

if(Metric=Margin,only('ABC'),0))

How to use the DB field ABC in the above two expressions . Is my requirement is clear or need more info

Regards

[priya945]

Hi Sweta,

As said, Sales and Margin are the two Matric(Field) values which contains weekly values as shown in above pic.

For Sales, I am calculating 'Avg of 3Weeks' as Sum(values) /3 in UI but for Margin - 'Avg of 3Weeks' is coming from a field called ABC which is already calculated in DB.

if(Metric=Sales,Sum(Values)/3,only('ABC'))

or

if(Metric=Sales,Sum(Values)/3,

if(Metric=Margin,only('ABC'),0))

How to use the DB field ABC in the above two expressions . Is my requirement is clear or need more info

Regards