Set Analysis and Custom Table Help Needed

[jcampbell474]

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.

[sunny_talwar]

Would you be able to share a sample where you have been playing around with this?

[jcampbell474]

Sunny T, you're always so quick to help.  Thank you!

I created a sample of what I'm doing.  It's attached.

[sunny_talwar]

Can you try now

[jcampbell474]

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!!!

[sunny_talwar]

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

[jcampbell474]

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)

[sunny_talwar · Accepted solution]

Check now

[jcampbell474]

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.