Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Does something look wrong with this formula?
sum({<TotNumber={'60','61','62'},SalesDate={'05/05/2010'}>}if(isnull(EndDate),Amount))
This formula works, but I also want to select by date.
sum({<TotNumber={'60','61','62'}>}if(isnull(EndDate),Amount))
My main goal is to develop a report that shows Daily Sales in columns so I can see the previous 7 days worth of sales.
I don't see any error.
Could you send an exemple of your application to illustrate this. You can reduce your datas and scramble them in document properties / scrambling if you want.
Martin
In case it matters, QlikView is sensitive to the date format used in set analysis. So the '05/05/2010' will match a SalesDate in 05/05/2010 format, but not 05/05/10 format, for instance. Also, I recommend len(EndDate)<1 instead of isnull(EndDate) due to some implementation differences with, I believe, the 64-bit version. But no, I'm not actually seeing an error.
5/5/2010 12:00:00AM
this is the actual format that is pulled in another application, I tried to type this exactly and it still doesn't work. Is it possible to set the format of the Date in the set analysis formula?
Yes, you can set the format of the date in the set analysis expression. For instance, I think this:
sum({<TotNumber={'60','61','62'},SalesDate={'$(=date(date#(20100505,'YYYYMMDD'),'D/M/YYYY hh:mm:ss TT'))'}>}if(isnull(EndDate),Amount))
BUT that isn't what I'd do. Instead, I'd convert your date to your desired format as your read it into QlikView from your data source. Something like this:
date(date#(SourceSalesDate,'D/M/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as SalesDate
Since SalesDate will then be in the desired format, I believe your original expression would then work as is.
Ok, I got the formula to work as follows.
sum
In order to see the last 7 days worth of sales though I have to have 7 expressions with different dates in each expression. It works, but I have to change the date every day in each expression I would like to try to get it to calculated autimatically.
I've tried substituting the following in for the date SalesDate={'Today()-1'} and SalesDate={'Max(SalesDate)'}. but the formulas don't work then.
({<TotNumber={'60','61','62'},SalesDate={'5/2/2010 12:00:00 AM'}>} Amount)Ok, I got the formula to work as follows.
sum({<TotNumber={'60','61','62'},SalesDate={'5/2/2010 12:00:00 AM'}>} Amount)
In order to see the last 7 days worth of sales though I have to have 7 expressions with different dates in each expression. It works, but I have to change the date every day in each expression I would like to try to get it to calculated autimatically.
I've tried substituting the following in for the date SalesDate={'Today()-1'} and SalesDate={'Max(SalesDate)'}. but the formulas don't work then.
If you place your code or formulas between the words code and /code both enclosed in square brackets [ ] you get the nice gray box with fixed font and scroll bar ...
You have to inclose your date value in a $ expansion expression
Sum({<TotNumber={'60'},SalesDate={'$(=Max(SalesDate))'}>} Amount)
You should also make sure you use the same date format throughout your app.
Thanks, that formula works as I want it to. Now I want to create another column with the daily sales for the day before the date calculated previously. I tried adding -1 after $(=Max(SalesDate)) but it doesn't work. What's the trick?
I believe this would be the right syntax, but have not tested.
sum({<TotNumber={'60'},SalesDate={'$(=date(max(SalesDate)-1))'}>} Amount)