Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

set analysis

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.

14 Replies
Highlighted

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
MVP
MVP

You mean to say...

Current Month Sales - Average of Last Three Months Sales?

Highlighted
Specialist
Specialist

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))

Highlighted

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator
Creator

Or is it

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

Highlighted
Not applicable

I have given the variable definition as

vMaxSalesDate = Max(Date)

Highlighted
Not applicable

yes.

Current month values - Avg(last 3 months value)

Highlighted
Not applicable

No. its not working

Highlighted
MVP
MVP

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..