Daily Sales

[#1]

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.

[martin59]

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

[johnw]

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.

[#4]

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?

[johnw]

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.

[#6]

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)

[#7]

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.

[#8]

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.

[#9]

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?

[johnw]

I believe this would be the right syntax, but have not tested.

sum({<TotNumber={'60'},SalesDate={'$(=date(max(SalesDate)-1))'}>} Amount)