Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions

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

17 Replies
MVP
MVP

Re: Showing Info for the last 7 Days sales

Try redefining your variables with date() like:

MaxTransDate1 = date(MaxTransDate-1)

....

Edit: Corrected

stabben23
Honored Contributor

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)

Instead of variable you can add addmoths or today or any other datefunction

stabben23
Honored Contributor

Re: Showing Info for the last 7 Days sales

Ok, you want it in 7 different colums?

Not applicable

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

Not applicable

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

Not applicable

Re: Showing Info for the last 7 Days sales

Hi Tresesco

I have tried the formula, but does not work. 

kind regards

Nayan

Not applicable

Re: Showing Info for the last 7 Days sales

Hi Staffan

Yes, that is correct.

kind regards

Nayan

Not applicable

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

stabben23
Honored Contributor

Re: Showing Info for the last 7 Days sales

Can you upload som example?

Community Browser