topic Re: Average Sales by category in QlikView

Average Sales by category

AH — Sun, 21 Dec 2014 00:53:09 GMT

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

Re: Average Sales by category

jagan — Sun, 21 Dec 2014 04:11:05 GMT

Hi,

Try this expression

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

Regards,

Jagan.

Re: Average Sales by category

AH — Sun, 21 Dec 2014 05:04:07 GMT

Thanks Jagan! Your expression works perfect.

Shan

Re: Average Sales by category

AH — Sun, 21 Dec 2014 05:21:00 GMT

Hi Jagan,

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

Thanks,

Shan

Re: Average Sales by category

AH — Sun, 21 Dec 2014 18:06:17 GMT

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

Re: Average Sales by category

jagan — Mon, 22 Dec 2014 01:18:02 GMT

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.

Re: Average Sales by category

AH — Mon, 22 Dec 2014 01:38:22 GMT

Hi Jagan,

Tried with the expression. Not working.

Regards,

Shan

Re: Average Sales by category

jagan — Mon, 22 Dec 2014 01:49:52 GMT

Hi,

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

Regards,

Jagan.

Re: Average Sales by category

AH — Mon, 22 Dec 2014 01:53:18 GMT

Hi Jagan,

Please find attach file. Thanks for your quick response.

Regards,

Shan

Re: Average Sales by category

jagan — Mon, 22 Dec 2014 02:02:34 GMT

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.