Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Kushal_Chawda

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

View solution in original post

17 Replies
tresesco
MVP
MVP

Try redefining your variables with date() like:

MaxTransDate1 = date(MaxTransDate-1)

....

Edit: Corrected

stabben23
Partner - Master
Partner - Master

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
Partner - Master
Partner - Master

Ok, you want it in 7 different colums?

Not applicable
Author

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
Author

Hi

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

kind regards

Nayan

Not applicable
Author

Hi Tresesco

I have tried the formula, but does not work. 

kind regards

Nayan

Not applicable
Author

Hi Staffan

Yes, that is correct.

kind regards

Nayan

Not applicable
Author

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
Partner - Master
Partner - Master

Can you upload som example?