Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two issues that have effectively become a road block. I've tried many, many different approaches and cannot arrive at a solution that works 100%. They either do not work at all or partially. Here are my dilemmas:
1. I have three listboxes for the user to select what rows/columns they want in the straight table.. 1) Dimensions 2) Metrics 3) Date Views. Dimensions and Metrics work fine. When I select LYTD (Last Year to Date), the YTD column appears too. I assume it has to do with Wildmatch and/or the asterisks around LYTD.
Here is the conditional show expression I'm using for LYTD YTD is the same, with the exception of LYTD is replaced with YTD:
=WildMatch ( GetFieldSelections(_Metrics, '|'), '*NB*' ) and WildMatch ( GetFieldSelections(_Dates, '|'), '*LYTD*' ) = 1
I've tried Match, removed the asterisks, etc... I've also used =SubStringCount(Concat(_Columns, '|'), 'xxx') But nothing works.
Here is the inline table I'm loading:
Dates:
Load * Inline
[_Dates,_DateNum
Yr/Mth,00
YTD,01
LYTD,02
SMLY,03];
2. Set analysis and a date range. To populate the LYTD selection above, again, I've tried many different expressions. Here are a few:
=Sum({$<FiscalYearMonth={">=$(vpFYMStart) <=$(vprevcurrFYM)"}>} sold)
//sum({$<FiscalYear={'$(vpFY)'}>}sold)
//sum(if(FiscalYearMonth>='$(vpFYMStart)' and FiscalYearMonth<='$(vprevcurrFYM)',sold))
//Sum({$<FiscalYearMonth={">=$(vcFYMStart)-100 <=$(vcurrFYM)-100"}>} sold)
//Sum({$<FiscalYearMonth={">=$(vpFYMStart) <=($(vcurrFYM)-100)"}>} sold)
vpFYMStart = Previous Fiscal Year Month Start. Ex. 201500.
vprevcurrFYM = Previous (current) Fiscal Year Month. Mirrors the current FiscalYearMonth to one year ago.
vcurrFYM = Current Fiscal Year month.
Most of them work if a date is not selected. Once a date is selected, it zeros out. In the set analysis, $ works as opposed to 1. I need the user to be able to select a fiscal year and month(2) in 2016, and see the sum for the same date range in the previous year. I feel like it is or should be simple and I'm overthinking it. But, I could be wrong.
Any and all help will be greatly appreciated.
Check now
Would you be able to share a sample where you have been playing around with this?
Sunny T, you're always so quick to help. Thank you!
I created a sample of what I'm doing. It's attached.
Can you try now
That's it, you fixed it. So, putting pipes around the value is what it needed? I googled it for hours and didn't see that anywhere.
Did you get a chance to look at the Set Analysis? I really just need it to:
1. Show the same FiscalYearMonth of the previous year;
2. Show YTD of the current year (I got this one)
2. Show YTD of the previous year depending on the FiscalYearMonth the user selects.
Thank you so much!!!
The fiscalMonthYear field was not a date field. I changed that to a date field in the script because finding LYTD and other measure such as those are easier to work with when you have dates. See if the attached helps.
Best,
Sunny
I see where you changed the Fiscal date to fields to actual date fields, but the _Dates selection SMLY shows a zero (0) value when selected. I selected a month & year so that it will show.
It's this expression:
=Sum({$<FiscalYearMonth = {"$(=vYMOM)"}>} Sales)
Check now
That did it! Thank you, sir.
So, is it best practice to convert fiscal periods to calendar date? I can see using QV's built-in functionality making them easier to work with.
Thanks again for your help.