I know there are expressions in set analysis that allow for the ignoring of dimensions entirely, but I'm trying to retain the consideration of the first dimension, while ignoring the second dimension.
Take the attached photo as an example...
(The first dimension is NOT visible - Store name resides out to the left of the date).
You'll see that I have a date in the second dimension... this is linked to the Calendar and is set to only display the current week's total sales for a particular store (there are two stores total in the photo).
How can I write an expression that sums up all of the CUMULATIVE totals for the current week (therefore ignoring the date dimension visible on the far left).
As an example, column 5, row 1 should show $12731.74, row 2 $9715.04, etc Column 5 row 5 should show 10068.47, row 2 $7698.74, and so on down the line...
Maybe set analysis isn't the most efficient way to go here.. I'm open to suggestions.
I also know without an actual QVD/work file to see as an example, it's difficult, so I hope I provided enough information in the photo and detailed here.
This is one of the better software support forums I've come across in the IT industry - you guys are great!
you can consider as a working assumption that EVERYTHING IS POSSIBLE within QlikView, the question is how to do it better...
So, one way to accumulate sales is to use function ABOVE() - using the value from the previous row in the chart.
Another way would require a bit more data modeling, and it involves building another Calendar table - we usually call it "As Of Date" (look it up in the forum, there were plenty detailed explanations about that)
A third way would involve a so called "Date Island" - a separate calendar table, not linked to anything (this one is fairly heavy on large data sets).
don't get confused between "ignoring selections" and "ignoring dimensions". Set Analysis deals with selections, not dimensions. Actually, it's sort of "blind" to the dimension values because SA expression is evaluated once for the whole chart (not in every row).
For your need, in order to show a weekly total, disregarding dates, you can use prefix TOTAL:
sum(TOTAL <Week> Sales)
Triangular brackets <> are part of the syntax here. It's important to know that the aggregation field (Week in this example) needs to be one of the Chart Dimensions, even if it's hidden.
You're right, I was confusing two terms and tossing them around ignorantly, but I'm glad you grasped what I was asking anyway 🙂
This is absolutely fantastic Oleg, and pointed me in the right direction... but it's not 'exactly' what I was looking for.. here's why:
Using... SUM(TOTAL <CalendarLink> Sales) (where CalendarLink is the date or second column) calculated both stores together (as expected given your explanation of the behavior) for any given date. So I would get both values for 4/13 added together and they would be identical for both stores 4/13 values.
OK, that made sense.. let's go instead and use the store name
Using... SUM(TOTAL <Store> Sales) (where Store is the first dimension not visible in the photo on the left) did EVERYTHING I expected it to, except the values were identical. So in other words, I was always getting the same value for each date to the right. All of the values were correct for the previous day, but I was hoping to show a cumulative roll-up, where the second row would be missing the first rows' sales (ie: 4/12 would not show the total for 4/13 also).
Is this feasible or am I trying too hard to meet expectations on what is visible all within one object?
you can consider as a working assumption that EVERYTHING IS POSSIBLE within QlikView, the question is how to do it better...
So, one way to accumulate sales is to use function ABOVE() - using the value from the previous row in the chart.
Another way would require a bit more data modeling, and it involves building another Calendar table - we usually call it "As Of Date" (look it up in the forum, there were plenty detailed explanations about that)
A third way would involve a so called "Date Island" - a separate calendar table, not linked to anything (this one is fairly heavy on large data sets).
