8 Replies Latest reply: Jul 17, 2013 10:09 AM by David Radczyc

# Sum sales after specific date

Hello,

I want to calculate Sales after specific date. I have existing calendar (CalendarDate), but I want to use date range from Excel file.

New data will be uploaded every week. This is Spreadsheet with specific dates (connected by Customer filed):

CustomerDate
Customer101/05/2013
Customer201/06/2013
Customer306/07/2013

I would like to calculate Sales after those dates and see this in Straight table. Is there anyway to do this in expression?

The problem is when I put =sum(Sales) will shows me the whole sales as normal, but I tried something like:

sum({<Date = {"\$(>=(Date))"}>}Sales)

It doesn't work. Any ideas please?

Thanks,

• ###### Re: Sum sales after specific date

Try this.

sum({<Date = {">=\$(=Min(Date))"}>}Sales)

Regards,

Kaushik Solanki

• ###### Re: Sum sales after specific date

Hi there, thanks for help, but still doesn't work. Maybe I will explain more and you can see in the table.

Total Sales =sum(Sales) and for

Sales After =sum({<Date = {">=\$(=Min(Date))"}>}Sales)

It shows me the same value.

Customer

Date
Total Sales
Sales After
Customer101/05/2013£150£150
Customer201/06/2013£150£150
Customer306/07/2013£200£200

Thanks,

• ###### Re: Sum sales after specific date

sum({<Date = {">=\$(=max(Date))"}>}Sales)

• ###### Re: Sum sales after specific date

Hi there. Thanks but still no values.

This Spreadsheet is linked by Customer to main table where are Customer,Sales,CalendarDate.

CalendarDate format is the same like Date format. When I choose dates from CalendarDate list it shows me correct values for few days or one specific day.

Am I missing something?

• ###### Re: Sum sales after specific date

Hi,

I guess the only way to know is you post your application. And tell us what exactly you need from application as output.

Regards,

Kaushik Solanki

• ###### Re: Sum sales after specific date

Hi,

so far I see your "Date" field is in text format. If you use functions like MAX or MIN you will get a number. So you are comparing a text with a number. It will not work.

To test this kind of behaviour go to:

Documents Properties / Number    select your Date filed and choose Integer.

To become more familiar with data formats read the article from Henric Cronström.

http://community.qlik.com/docs/DOC-3102

Good luck!

Rainer

• ###### Re: Sum sales after specific date

Thanks for good article, but Date works fine when I do table connection by Date to CalendarDate.

Thanks,

Dave

• ###### Re: Sum sales after specific date

In my perception, your problem seems to a specific date entry for each week.

So, what u can do is, u can have an seperate excel called Config.

In that you can hold the starting date for each week

Take it as a flag date in ur app.

Now u can use then following exp as,

sum({\$<Date = {">=\$(=Min(Date))"},FlagDate={'1'}>}Sales)