Sum sales after specific date

[#1]

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

Customer	Date
Customer1	01/05/2013
Customer2	01/06/2013
Customer3	06/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,

[kaushiknsolanki]

Try this.

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

Regards,

Kaushik Solanki

[#3]

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
Customer1	01/05/2013	£150	£150
Customer2	01/06/2013	£150	£150
Customer3	06/07/2013	£200	£200

Thanks,

[nizamsha]

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

[#5]

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?

[kaushiknsolanki]

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

[#7]

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

[parthiband]

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)

[#9]

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

Thanks,

Dave