Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Try this.
sum({<Date = {">=$(=Min(Date))"}>}Sales)
Regards,
Kaushik Solanki
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,
sum({<Date = {">=$(=max(Date))"}>}Sales)
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?
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
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
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)
Thanks for good article, but Date works fine when I do table connection by Date to CalendarDate.
Thanks,
Dave