topic Re: MonthEnd - 7 day sales in QlikView

MonthEnd - 7 day sales

vireshkolagimat — Sun, 18 Mar 2018 12:37:21 GMT

Hi All,

I am trying to get MonthEnd-7 days sales and i created the below expression. But in order to see last 7 day sales i have make selections. How i can make it fixed one.

=sum({<DateKey={">=$(=Date(Max(DateKey-6)),'YYYYMMDD')<=$(=Max(DateKey))"}>}SalesValue*SalesQty)

I am not looking for last 7 days but but it should be like monthend -7 days sales for each month.

If i choose Jan month, then it should show the Jan month sales(it looks fine) and last 7 days sales of Jan month.

The data should look like below.

Division	Jan Sales	Last 7 Day Sales
Consumer	1,000.000	30.000

Thanks for the support.

Regards,

Viresh

Re: MonthEnd - 7 day sales

swuehl — Sun, 18 Mar 2018 13:00:43 GMT

There is a function Monthend() to get the end of the month, use something like

=sum({<DateKey={">=$(=Date(Monthend(Max(DateKey))-6,'YYYYMMDD'))<=$(=Date(Monthend(Max(DateKey)),'YYYYMMDD'))"}>}SalesValue*SalesQty)

edit: Changed first search date

Re: MonthEnd - 7 day sales

swuehl — Sun, 18 Mar 2018 13:09:15 GMT

And remember to clear user selections in calender fields (other date fields) you need to ignore (like week, Date, Day):

=sum({<DateKey={">=$(=Date(Monthend(Max(DateKey))-6,'YYYYMMDD'))<=$(=Date(Monthend(Max(DateKey)),'YYYYMMDD'))"}, OtherDateOrWeekField= >}SalesValue*SalesQty)

Re: MonthEnd - 7 day sales

vireshkolagimat — Sun, 18 Mar 2018 13:09:58 GMT

Thank you. It looks good.

Re: MonthEnd - 7 day sales

vireshkolagimat — Sun, 18 Mar 2018 13:22:23 GMT

Yeah I did. thank you.