Aggregate and Non Aggregate together

Question & Answer
,
1

I have fields : date, ordered_quantity, amount, etc.
I need to create a line chart with a parameter to switch between metrics. This parameter would have values : Order Quantity, Amount, Avg Unit Price
Avg Unit Price = sum( {ordered_quantity})/ sum({amount})

how do I make this work?

my current calculated field is
ifelse ( ${param} = ‘Order Quantity’, {ordered_quantity},
${param} = ‘Amount’, {amount},
${param} = ‘Avg Unit Price’, {Avg Unit Price},
NULL)

This gives and aggregate non aggregate error. how to implement this

2

Create other calculations for Ordered_quantity as sum,avg or what ever you want. same for amount as well.
then you try to create the below calculation.
sum({amount}) - name it as Sum_Amount
sum({ordered_quantity}) - name it as Sum_ordered_quantity

felse ( ${param} = ‘Order Quantity’, {Sum_ordered_quantity},
${param} = ‘Amount’, {Sum_Amount},
${param} = ‘Avg Unit Price’, {Avg Unit Price},
NULL)
Try this.

3

Is the above mentioned calculation is working?

4

Hey Raju, thanks for the response, it did not work out. since my avg unit price is sum( {ordered_quantity})/ sum({amount}), this is calculated in qs. Instead the below worked

ifelse ( ${param} = ‘Order Quantity’, SUM( {ordered_quantity}),
${param} = ‘Amount’, SUM({amount}),
${param} = ‘Avg Unit Price’, {Avg Unit Price},
NULL)
1 Like