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:
SALES_SELLOUT .DATE >= varMinDATE
AND SALES_SELLOUT.DATE <= varMaxDATE
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:
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
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.
First thank for your answer. I've been doing some tests with it in QV 7.5 and it doesn't work, so i tried on QV 8.5, and the example you attached works fine, but it has one problem. Use
just works if CUSTOMER has 1 value selected, so if i select 'TATA' and 'TITI' the expression shows nothing. To try it i've created a table with CUSTOMER as dimension and in the expression i used
$(='var_'&CUSTOMER). What i need is that the expression works also when multiples values are selected for CUSTOMER.
I think what i need isn't possible in Qlikview, anyway what you told me seems to be very usefull in other situations, so thanks a lot for the information!
P.D.: I attached your QV example with the table i used to test the dynamic variables. Please if you have a moment check it in case i did something wrong and it really works with multiple values selected on CUSTOMER.
Tests_Variables.qvw 113.2 K
Can you not use the same solution as was posted to your other query?
I have attached a similar file with Store as the main dimension.
DateRange_DynamicVars.qvw 116.5 K
Yeah, it works for my other post too. I was trying to reach the same goal on both posts, but from diferent ways. Anyway at the end i had to make it other way because i had some problems with the QV version. I'll try to change to this solution when i was able to update the version.
Thanks for your time and effort ^^, hope i can help you whenever you have some problem.