topic set analysis in QlikView

set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I need to do an expression which shows difference of current month value & last 3 months average value.

I did the expression as

(Sum({< MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}sales)

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Workingdays))

Avg(aggr(

(Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} sales)

Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Workingdays))

,Branch))

Is the expression wrong? if so, Please help me with the correct expression.

Re: set analysis

jonathandienst — Thu, 28 Apr 2016 09:16:33 GMT

It depends on how vMaxSalesDate is defined and the format of the dates.

Re: set analysis

MK_QSL — Thu, 28 Apr 2016 09:16:48 GMT

You mean to say...

Current Month Sales - Average of Last Three Months Sales?

Re: set analysis

ajsjoshua — Thu, 28 Apr 2016 09:17:25 GMT

Hi,

Try this

(Sum({< MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}sales)

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Workingdays))

Rangeavg(aggr(

(Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} sales)

Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Workingdays))

,Branch))

Re: set analysis

jonathandienst — Thu, 28 Apr 2016 09:19:32 GMT

What do you get if you add a text box with the expression =vMaxSalesDate?

The expression will show the daily average of the last month compared to the daily average of the previous 3 months. Is that what you require?

Re: set analysis

ahammadshafi — Thu, 28 Apr 2016 09:21:14 GMT

Or is it

Average sales of current month - average sales of last three months?

Re: set analysis

Thu, 28 Apr 2016 09:22:13 GMT

I have given the variable definition as

vMaxSalesDate = Max(Date)

Re: set analysis

Thu, 28 Apr 2016 09:23:05 GMT

yes.

Current month values - Avg(last 3 months value)

Re: set analysis

Thu, 28 Apr 2016 09:23:52 GMT

No. its not working

Re: set analysis

MK_QSL — Thu, 28 Apr 2016 09:29:39 GMT

If you say Current Month Sales, Do you mean by MTD or Avg Sales per Day for this month?

Because if you consider MTD, what if the current date is 2nd... You only have 2 days sales and final result would be negative..

Re: set analysis

Thu, 28 Apr 2016 09:37:03 GMT

yes. current month means MTD.

i need to provide data for the daily improvement.

even if it is negative, no problem

Re: set analysis

sunny_talwar — Thu, 28 Apr 2016 10:21:10 GMT

Have you checked if each of the component alone is working as you would expect? I see 4 expressions, I would suggest to break them down into 4 expressions to check if one of them is not behaving or if all is not behaving. Once you have that, we can move forward from there.

Also try the below expression, I made some minor enhancements and deleted some stuff which was not really necessary:

(Sum({<MonthYear = , Date = {">=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))"}>}sales)

Sum({<MonthYear =, Date = {">=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))"}>} Workingdays))

(Avg(Aggr(Sum({<MonthYear =, Date = {">=$(=Date(MonthStart(vMaxSalesDate, -3)))<=$(=Date(MonthEnd(vMaxSalesDate, -1)))"}>} sales)

Sum({<MonthYear =, Date = {">=$(=Date(MonthStart(vMaxSalesDate, -3)))<=$(=Date(MonthEnd(vMaxSalesDate, -1)))"}>} Workingdays), Branch)))

Re: set analysis

jagan — Thu, 28 Apr 2016 16:24:06 GMT

Hi,

Try this

=(Sum({< MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}sales)

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Workingdays))

(Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} sales)

Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Workingdays))

I am not sure why you are using Aggr with Branch?

Regards,

Jagan.

Re: set analysis

Thu, 28 Apr 2016 16:33:35 GMT

hi Jagan,

i tried this without Aggr.

but it is not giving average

Re: set analysis

jagan — Thu, 28 Apr 2016 16:39:46 GMT

Hi,

Can you attach sample file and your expected result for particular selections, so that it would be easier to understand and provide solution.

Regards,

Jagan.