
1. Re: A way to count records, meeting certain criteria
Jim Wahl Feb 13, 2013 11:36 PM (in response to Armando Ramirez)Hi Armando,
The below table calculation will count stores with sales > 0. The FIRST()==0 part prevents overlapping textthe calc is the same for every row in the partition so we only need to display the first.
IF FIRST()==0 THEN
WINDOW_SUM(SUM(IF [Sales at STORE Level] >= 0 THEN 1 ELSE 0 END))
END
Assuming you want region and product in the view, you'll need to have Store on the level of detail shelf. On each table calculation pill, click and select Compute using > Store
You can use these same table calculations to sum at the region level (the subtotals above):
To be safe here, you want to have Store and Product on the detail shelf. And, on each table calc pill, select Edit Table Calculation > Compute Using > Advanced. Move Region, Store and Product to the right box (in that order). Click OK and select At the level > Deepest and Restarting every > Region.
See attached.
Jim

salesPerStore.twbx.zip 22.8 KB
