8 Replies Latest reply: Oct 1, 2018 9:12 AM by Priya LK

# Help with Expression

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

• ###### Re: Help with Expression

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

• ###### Re: Help with Expression

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

• ###### Re: Help with Expression

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.

• ###### Re: Help with Expression

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

• ###### Re: Help with Expression

HI ALL

• ###### Re: Help with Expression

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.

• ###### Re: Help with Expression

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

• ###### Re: Help with Expression

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