topic Re: Sum formula with criteria similar to sumifs in QlikView

Sum formula with criteria similar to sumifs

Fri, 27 Sep 2013 15:16:32 GMT

I have a comparison table that draws data from two different containers with list boxes that I use to filter my comparison results. One column sums up the sales from one container set and the next column sums up the sales from the other container set and then I have a column that shows the difference. The sum formula looks like this: =Sum( {EventComp1} [# of Seats] ) where EventComp1 is the alternate state of one of the containers and the other sum formula just replaces EventComp1 with EventComp2 to get the second set for comparison.

I would like to add some criteria that I can use to filter the whole comparison table to show me the sales that happened prior to 30 days from the event that I'm comparing. So simply put it would be "sum the sales from EventComp1 that happened before 30 days to the event (or whatever number of days I specify)" and then I would do that same for EventComp2 and the comparison column would give me the difference.

I'm using an input box with a variable called DaystoEvent and so I thought the formula would look something like this:

SUM({EventStartDate - SaleDate>=DaystoEvent},{EventComp1} [# of Seats])

This obviously didn't work, but I can't seem to find the correct syntax for it to work.

Re: Sum formula with criteria similar to sumifs

tresB — Fri, 27 Sep 2013 16:13:26 GMT

I would suggest to see this discussion

Re: Sum formula with criteria similar to sumifs

Fri, 27 Sep 2013 16:35:23 GMT

I looked at your link and based on that I've come up with the following, but it still tells me I have an error in my expression. Can you see where I'm going wrong?

Expression: = Sum( {EventComp1} <DayStart([EventStartDate]) - DayStart([UpdateDate]) >= [DaystoEvent] > [# of Seats] )

Re: Sum formula with criteria similar to sumifs

tresB — Fri, 27 Sep 2013 16:46:07 GMT

May be like this:

Sum( {EventComp1 < [DaystoEvent]={"$(=(DayStart([EventStartDate]) - DayStart([UpdateDate])) >} [# of Seats] )

Assuming DaystoEvent is a numeric field.

Re: Sum formula with criteria similar to sumifs

Fri, 27 Sep 2013 17:00:48 GMT

DaystoEvent is a numeric field, so you were right in your assumption.

However, your solution didn't quite work. I get "Expression OK" at the top of the Edit Expression box, but the column only returns "-". When the expression is pasted in the dialog box the open parenthesis after "Sum" shows red and there is a red error "^" underneath the second "{". Any ideas what it could be?

Re: Sum formula with criteria similar to sumifs

tresB — Fri, 27 Sep 2013 17:11:38 GMT

Sorry. it was a mistake. Try:

Sum( {EventComp1 < [DaystoEvent]={"$(=(DayStart([EventStartDate]) - DayStart([UpdateDate])) "} >} [# of Seats] )

"" and '}' was missing.

Re: Sum formula with criteria similar to sumifs

Fri, 27 Sep 2013 17:18:14 GMT

It still doesn't quite work and I'm still new enough to not be able to troubleshoot. I don't know if it helps, but I've added a screen shot of what it looks like.

Re: Sum formula with criteria similar to sumifs

Fri, 27 Sep 2013 17:19:31 GMT

I just realize you also add a quotation, but it still doesn't change it when I put that in as well.

Re: Sum formula with criteria similar to sumifs

tresB — Fri, 27 Sep 2013 17:44:47 GMT

One more correction: ')' was missing;

Sum( {EventComp1 < [DaystoEvent]={"$(=(DayStart([EventStartDate]) - DayStart([UpdateDate])) ) "} >} [# of Seats] )

If this doesn't work, please share your sample app.

Re: Sum formula with criteria similar to sumifs

Fri, 27 Sep 2013 18:01:37 GMT

How would I pair down the app to be able to share? Currently it shows as 200MB on my network. Also, is there a way to share without putting all my company's data on the web?