Thank for your effort Eugene. It seems what i want is not possible, and its a pitty because i think it's a very usefull option. Anyway ill explain more in detail my problem and maybe find another way to solve the problem:
*** We have 3 table:
- SALES_SELLOUT: Table containing the sales of a product in a month from some store.
- PRODUCTS: Table containing all the info about products, and related with SALES_SELLOUT.
- MONTHS: Table containing all the months from the first month in SALES_SELLOUT to the last month, even if there is no sale in the month. This table is NOT related with other tables.
*** There is 2 variables:
- varMinDATE: Stores the MIN date selected from the table MONTHS ---> =DATE(MIN(MONTHS.DATE),'DD/MM/YYYY')
- varMaxDATE: Stores the MAXdate selected from the table MONTHS ---> =DATE(MAX(MONTHS.DATE),'DD/MM/YYYY')
*** There is 1 filter:
- For the field MONTHS.DATE
*** There is one pivot table with some dimensions from SALES_SELLOUT and PRODUCTS tables (isn´t important since they are related). And there is one expression with this:
SUM(
IF(
SALES_SELLOUT .DATE >= varMinDATE
AND SALES_SELLOUT.DATE <= varMaxDATE
, SALES_SELLOUT.VOL
)
)
At this point, when you select some months in the MONTHS.DATE filter, the pivot table shows the Volume for the sales included in that period of time. Because other tables on the document, i can't use the SALES_SELLOUT.MONTH as the filter, that's why i use another table to filter the months.
The problem comes here, now i want that the pivot table just shows me the Volume of the sales in that period of time but just from the stores that have sales in ALL the months selected in the filter. So if you select from january'09 to march'09, the stores that didn´t have sales on those 3 months, won´t be used to calculate the Volume.
My first approach to solve the problem was to make another table with all DISTINCT STORES, MONTH from the SALES_SELLOUT table, and related with the MONTHS table, so the fields from the new table were: AUX.STORE,AUX.DATE and MONTHS.DATE. So when you select the months in the filter, the table will have 1 line for every store that has sales in the months selected. Lets suppose Store A has sales all the months, B also has sales all the months, and C has sales all the months except march'09. Then we select in the months filter from jan'09 to april'09. In the new table there will be 4 lines for store A (jan,feb,mar,apr), 4 lines for store B (jan,feb,mar,apr), and 3 lines for C (jan,feb,apr).
The next step was to change the filter on the expression, but there was where Qlikview stopped me. The perfect expression would be:
SUM(
IF(
COUNT(DISTINCT IF(AUX.STORE = SALES_SELLOUT.STORE, AUX.DATE) ) = COUNT(TOTAL DISTINCT MONTHS.DATE)
AND SALES_SELLOUT .DATE >= varMinDATE
AND SALES_SELLOUT.DATE <= varMaxDATE
, SALES_SELLOUT.VOL
)
)
The problem is Qlikview just let me do this if i put TOTAL on the COUNT, to end like this:
COUNT(TOTAL DISTINCT IF(AUX.STORE = SALES_SELLOUT.STORE, AUX.DATE) ) = COUNT(TOTAL DISTINCT MONTHS.DATE)
But that will count all the distinct AUX.DATES, without taking in consider the store. I tried some variants with same result, and then was when i started with variable shot.
I hope this clarify you a bit more Eugene, and maybe iluminate you with some solution.
Thank beforehand!
