Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've discovered Alternate States and am working on implementing them in a tab that will allow the user to select two distinct periods to compare. This year for example, the users often want to compare the week with the week +1 of last year to line up the weeks (leap year...).
I have two multi-boxes that allow the user to select year(s), year-week(s), date(s)
The pivot will then allow comparing the data over the two selected periods A, and B. My alternate states are called [Période A] and [Période B]
My problem is the current year and month are selected by default on opening the app, so I'd like my pivot table to ignore the year, month, or even selected dates from the default state but include any other selection.
I've tried the following syntaxes :
1./ Sum( {[Période A]*$<TYPE_CALENDRIER={'N'}, ORIGIN={'FACTURES'} >} VOLUME ) => this gives 0 if the current year <> state year etc.
2./ Sum( {[Période A]*$<TYPE_CALENDRIER={'N'}, ORIGIN={'FACTURES'}, YEAR, YEAR_WEEK, DATE_JOUR>} VOLUME ) => this gives a huge number I can't explain (the sum of all dates maybe?)
3./ Sum( {<TYPE_CALENDRIER={'N'}, ORIGIN={'FACTURES'}, YEAR=[Période A]::YEAR, YEAR_WEEK=[Période A]::YEAR_WEEK, DATE_JOUR=[Période A]::DATE_JOUR>} VOLUME ) => this gives 0 if the current year <> state year etc.
The only way I can get the chart to work correctly is to clear the selected date fields on entering the sheet, but I don't like this cheater work-around, as the user will then have to re-select the dates on leaving the sheet...
What am I missing here? A lot I'm sure.... but if you have any ideas for this problem I would really appreciate it!
