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: 
jjordaan
Partner - Specialist
Partner - Specialist

Sales per date for 10 days earlier

Hello everyone,

I hope somebody can help me with this.

A customer needs to have a Straight or pivot table with the dimension date.

For me this is the easy part 🙂

As an expression I need a formula that is calculating the sales but only for 10 days earlier.

See the example below

DateSalesSales 10 days Earlier
2013-11-21100 for the date 2013-11-21This should be the sales from the date 2013-11-11
2013-11-20250 for the date 2013-11-20This should be the sales from the date 2013-11-10
2013-11-19175 for the date 2013-11-19This should be the sales from the date 2013-11-09
2013-11-18150 for the date 2013-11-18This should be the sales from the date 2013-11-08

This table also needs to change to a line graph.

Thanks in advance for your time

1 Solution

Accepted Solutions
Gysbert_Wassenaar

While technically you can create an expression to calculate this (see Evaluating "sets" in the context of a dimension) I think you're better off adding a table in your data model that links every date with its 10-days earlier partner-date. A kind of AsOf table (see for example Calculating rolling n-period totals, averages or other aggregations). The AsOf table you'd need would be something like:

ReportDate, Date, Type

2013-11-21,2013-11-21,Current

2013-11-21,2013-11-11,10DaysEarlier

2013-11-20,2013-11-20,Current

2013-11-20,2013-11-10,10DaysEarlier

...etc

You can then use ReportDate as dimension and expressions like sum({<Type={'Current'}>}Sales) and sum({<Type={'10DaysEarlier}>}Sales).


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

While technically you can create an expression to calculate this (see Evaluating "sets" in the context of a dimension) I think you're better off adding a table in your data model that links every date with its 10-days earlier partner-date. A kind of AsOf table (see for example Calculating rolling n-period totals, averages or other aggregations). The AsOf table you'd need would be something like:

ReportDate, Date, Type

2013-11-21,2013-11-21,Current

2013-11-21,2013-11-11,10DaysEarlier

2013-11-20,2013-11-20,Current

2013-11-20,2013-11-10,10DaysEarlier

...etc

You can then use ReportDate as dimension and expressions like sum({<Type={'Current'}>}Sales) and sum({<Type={'10DaysEarlier}>}Sales).


talk is cheap, supply exceeds demand
Not applicable

Something like this should work:

=sum({< Date = {'$(=date(today(0)-10))'}>} Sales)

Best,

Matt

Not applicable

Hi Jeroen,

Did either of these solutions work for you?

Matt

jjordaan
Partner - Specialist
Partner - Specialist
Author

Sorry for the late reply.

Thank you for your help.

jjordaan
Partner - Specialist
Partner - Specialist
Author

Gysbert,

Thank you (as always) for your help.