topic Sum and Count for each column in QlikView

Sum and Count for each column

Fri, 31 May 2013 20:37:06 GMT

With the following two expressions I want to sum all sales from all Ship To's under it's respective Sales Catalog Section as well as count the records of Ship To's for each. So, only include records that had Device sales, records that had Extract sales, etc. I know saying "*" gives me all Ship To's, but I would have expected it to consider the Sales Catalog Section it's under? And yes, I do need the total at each record because I will eventually use these two expressions in another record level expression. What am I missing?

Sum(TOTAL {<[Ship To]={"*"}>} [Sales Amount])

Count(TOTAL {<[Ship To]={"*"}>} [Ship To])

Re: Sum and Count for each column

swuehl — Fri, 31 May 2013 21:08:11 GMT

You probably don't need to use set analysis here. Try

sum(total<[Sales Catalog Section]> [Sales Amount])

Re: Sum and Count for each column

Mon, 03 Jun 2013 15:19:25 GMT

That work great swuehl, thank you! I had over simplified it though in my example. I do need to use set analysis I believe because I also need to specify the previous two fiscal years, all months, and all ship to's. WIth your help, this is what I have now that works:

Sum(TOTAL <[Sales Catalog Section]> {$<[Fiscal Year]={'>=$(=FiscalCurrentYear-2)<$(FiscalCurrentYear)'},Month={"*"},[Ship To]={"*"}>} [Sales Amount])

I would also like to include this in a label of an expression although it doesn't seem to break down to Sales Catalog Section like the value does. Any idea why?