Average Sales by category

[AH]

Hi,

Could you please have a look at the qvw and let me know that why i am not getting the eaxct Average sales amount for SalesType-FT and Product-UV?

Really appreciate any kind of help.

Thanks,

Shan

[jagan · Accepted solution]

Hi,

Try this expression

=Sum({<Product={UV}, SalesType={FT}>} [Sales Amount])/Count({<Product={UV}, SalesType={FT}>}Distinct CustomerID)

Regards,

Jagan.

[AH]

Thanks Jagan! Your expression works perfect.

Shan

[AH]

Hi Jagan,

How i can calculate the Six Months Moving Average Sales for Product UV and Prodct FT in the Chart?

Thanks,

Shan

[AH]

Hi Jagan,

Would it be the right expression if i want to calculate 6 months moving average?

=avg( aggr( rangesum( above( Sum({<SalesType={ST,SV}>} [TotalSalesAmount]),0,6) ),[Invoice Month]))

Thanks,

Shan

[jagan]

Hi,

If you have a date field in your Data model then try below expression to get last 6 months sales

Sum({<YearDimension=, MonthDimension=, Date={'>=$(=MonthStart(Max(Date), -5))<=$(=MonthEnd(Max(Date)))'}, SalesType={ST,SV}>} [TotalSalesAmount])

Hope this helps you.

REgards,

Jagan.

[AH]

Hi Jagan,

Tried with the expression. Not working.

Regards,

Shan

[jagan]

Hi,

Attach same file and your expected output, I think may be the date format issue.

Regards,

Jagan.

[AH]

Hi Jagan,

Please find attach file. Thanks for your quick response.

Regards,

Shan

[jagan]

Hi,

Try like this

=Sum({<[Inv Quarter]=, [Inv Week]=, [Inv Month], [Inv Year]=, InvDate={'>=$(=MonthStart(Max(InvDate), -5))<=$(=MonthEnd(Max(InvDate)))'}, SalesType={FT,SV}>} [Sales Amount])

Regards,

Jagan.