Nested iif in query

[nosaj03]

Im working on an hourly report that shows summarized sales data.

In my example Im capturing the sales at site A,B and C every hour. I also need to show the total sales of the entire group.

In the report I need to show the total for each site and a separate total for all sites for the previous hour, the total for the day up to the previous hour and the total sales done for the previous day.

Im attempting to do this all in one query so I can create a table with other indicators but the issue Im running into is when Im attempting to use the iif statement Im getting an error.

In my table I have Hours, Site, and Work.

How would I write the iif statement to summarize the data at the different level.

Below is what Im trying to do conceptually.

Hourly
System:iif(hours=now()-1,sum(work),0)
Site A:iif(hours=now()-1 and site=a,sum(work),0)
etc

Entire Day through the previous hour
System:iif(hours>=1 and <=now()-1,sum(work),0)
Site A: iif(hours>=1 and <=now()-1 and site=a,sum(work),0)
etc

I dont know if I make sense but any help would be greatly appreciated.

 

[Ranman256]

If you need to nest that many IIFs, then put them in a function.

 

[arnelgp]

Hourly
System:Sum(iif(hours=now()-1,[work],0))
Site A:Sum(iif(hours=now()-1 and site=a,[work],0))
etc

Entire Day through the previous hour
System:Sum(iif(hours>=1 and <=now()-1,[work],0))
Site A: Sum(hours>=1 and <=now()-1 and site=a,[work],0))