Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I'm creating a series of metrics to be contained in a table, all of which count the same thing (Transactions) by Merchant (the dimension) but each column measures the transactions within different time periods, e.g. last month, year-to-date, last year same month etc. Ideally, the user should be able to select just those merchants for whom they want to view the measurements, but the time periods should be static - no selections of day, month, date etc should change the metrics.
So for example, the column called 'This Month' should count all transactions which were recorded in the current month, and should not change regardless of date selection. However, the user may only choose to view ten merchants at a time, or all 1000+.
I have tried:
COUNT( {1<[Date.autoCalendar.$Date]= {">=$vCurrentMonthStart"},
[Date.autoCalendar.$Year]=,
[Date.autoCalendar.$Month]=,
[Date.autoCalendar.$Date]=,
[Merchant]=P([Merchant]) >}
Transactions
)
...which returns 0 and
COUNT({1<[Merchant]=P([Merchant])>}
IF([Date.autoCalendar.$Date]>=vCurrentMonthStart,
[Transactions]))
...which returns values but doesn't prevent date filters changing the measurement.
Some additional points: the problem doesn't lay with the variables nor the date formatting (they work fine elsewhere), I cannot list every possible value for the Merchants.
Any help would be greatly appreciated!
Steph