Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Avg of sales

Hi ,

Could someone pls help me in getting this scenario. I have 20 products in Product Field where i want to get the avg sales of this week and last week by each product in a chart.

Thnx,

John

1 Solution

Accepted Solutions
MVP
MVP

Current Week

Avg(Aggr(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today())))<=$(=Date(WeekEnd(Today())))"}>}Sales),Product))

or

Simply

Avg(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today())))<=$(=Date(WeekEnd(Today())))"}>}Sales))


Last Week

Avg(Aggr(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today()-7)))<=$(=Date(WeekEnd(Today()-7)))"}>}Sales),Product))

or

Simply

Avg(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today()-7)))<=$(=Date(WeekEnd(Today()-7)))"}>}Sales))

View solution in original post

6 Replies
MVP
MVP

Current Week

Avg(Aggr(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today())))<=$(=Date(WeekEnd(Today())))"}>}Sales),Product))

or

Simply

Avg(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today())))<=$(=Date(WeekEnd(Today())))"}>}Sales))


Last Week

Avg(Aggr(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today()-7)))<=$(=Date(WeekEnd(Today()-7)))"}>}Sales),Product))

or

Simply

Avg(SUM({<InvoiceYear = , InvoiceQuarter = , InvoiceMonth =, InvoiceWeek = , InvoiceDate = {">=$(=Date(WeekStart(Today()-7)))<=$(=Date(WeekEnd(Today()-7)))"}>}Sales))

View solution in original post

Try like this:

Dimension: Product

Expression: current week:

                   if(week(your_date_column)=week(today()) ,avg(sales))

                   previous week:

                    if(week(your_date_column)=week(today())-1 ,avg(sales))

this should solve the problem

Specialist III
Specialist III

Manish solution should work with what you are asking for

Creator II
Creator II

Sorry Guys, I missed few points here the data is not being reloaded every day...its like every three days ..usually every monday &  Thursday. So how do we go in this situation

Thnx,

John

MVP
MVP

Doesn't matter how frequently your data is being loaded. That will not make change on This week or last week.

Creator II
Creator II

Thanks Manish

Thnx,

John