17 Replies Latest reply: Dec 22, 2015 7:36 AM by Nayan Lalla

# Showing Info for the last 7 Days sales

Hi

I am creating a chart table where I want to show Sales Quantity per day, for the last 7 days sales.  The 2 dimensions are [Trans Date] & [Sales Qty] .

The last date (or max date) sales quantity formula works (see formula below), however when I apply similar formula to the other dates, it does not work. I have used variable (from variable overview) for my formulas. (see screen shot attached)

Please can you tell me where I am going wrong in my formula

Does work

=SUM({<[Trans Date]={'\$(MaxTransDate)'}>}[Sales Qty])  - This works

Don't work

=SUM({<[Trans Date]={'\$(MaxTransDate1)'}>}[Sales Qty])

=SUM({<[Trans Date]={'\$(MaxTransDate2)'}>}[Sales Qty])

=SUM({<[Trans Date]={'\$(MaxTransDate3)'}>}[Sales Qty])

=SUM({<[Trans Date]={'\$(MaxTransDate4)'}>}[Sales Qty])

=SUM({<[Trans Date]={'\$(MaxTransDate5)'}>}[Sales Qty])

=SUM({<[Trans Date]={'\$(MaxTransDate6)'}>}[Sales Qty])

=SUM({<[Trans Date]={'\$(MaxTransDate7)'}>}[Sales Qty])

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

Try redefining your variables with date() like:

MaxTransDate1 = date(MaxTransDate-1)

....

Edit: Corrected

• ###### Re: Showing Info for the last 7 Days sales

Hi

Sorry for relying late.  Will try the formula and let you know.

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

Hi Tresesco

I have tried the formula, but does not work.

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

Hi,

You should try to use it in the same expression, sum({<Date={">=\$(From) <=\$(To)"}>} Value)

• ###### Re: Showing Info for the last 7 Days sales

Hi Staffan

The formula that I have , I have taken it from a formula that our Outsource company has created.  In his formula he has "MonthIndex' (which is Fiscal Year x 12 + Fiscal Period) , which shows the sales quantity per month for the last seven months.

So i used the same formula and just substituted MonthIndex for [Trans Date].

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

Hi Staffan

I would love you load my QV Model but its too big and info is sensitive.  I'll make a sample using excel as my database.

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

Ok, you want it in 7 different colums?

• ###### Re: Showing Info for the last 7 Days sales

Hi Staffan

Yes, that is correct.

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

You must ignore the temporal dimensions that affect the calculation. I imagine analysis similar to September 1 :

=SUM({<[Trans Date]={'\$(MaxTransDate1)'},Date=>}[Sales Qty])

• ###### Re: Showing Info for the last 7 Days sales

Hi Roberto

Thank you for your feedback.  However the formula does not work.  There is a red underline on the second date.  See screenshot attached.

kind regards

Nayan

• ###### Re: Showing Info for the last 7 Days sales

Me podías pasar el fichero .qvw

Lo de la expresión Date era un ejemplo. No conozco el modelo que tienes montado. Pero quédate con la idea de obviar los filtros y/o selecciones temporales.

Saludos.

• ###### Re: Showing Info for the last 7 Days sales

Date is the selection date you use, Date here is just a dummyfield I guess.

• ###### Re: Showing Info for the last 7 Days sales

yes, that is correct

• ###### Re: Showing Info for the last 7 Days sales

try something like this...

=SUM({<[Trans Date]={'\$(date(MaxTransDate1))'}>}[Sales Qty])

• ###### Re: Showing Info for the last 7 Days sales

=SUM({<[Trans Date]={"\$(=date(max([Trans Date])-1))"}>}[Sales Qty])

=SUM({<[Trans Date]={"\$(=date(max([Trans Date])-2))"}>}[Sales Qty])

=SUM({<[Trans Date]={"\$(=date(max([Trans Date])-3))"}>}[Sales Qty])

.

.

=SUM({<[Trans Date]={"\$(=date(max([Trans Date])-7))"}>}[Sales Qty])

• ###### Re: Showing Info for the last 7 Days sales

Hi Kush

Your formula worked.  Thanks .  Much appreciated.

kind regards

Nayan