topic Re: Set Analysis and Custom Table Help Needed in QlikView

Set Analysis and Custom Table Help Needed

jcampbell474 — Tue, 24 May 2016 00:25:24 GMT

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.

Re: Set Analysis and Custom Table Help Needed

sunny_talwar — Tue, 24 May 2016 01:37:43 GMT

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

Re: Set Analysis and Custom Table Help Needed

jcampbell474 — Tue, 24 May 2016 13:03:22 GMT

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

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

Re: Set Analysis and Custom Table Help Needed

sunny_talwar — Tue, 24 May 2016 13:16:39 GMT

Can you try now

Re: Set Analysis and Custom Table Help Needed

jcampbell474 — Tue, 24 May 2016 13:27:28 GMT

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

Re: Set Analysis and Custom Table Help Needed

sunny_talwar — Tue, 24 May 2016 14:07:20 GMT

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

Re: Set Analysis and Custom Table Help Needed

jcampbell474 — Tue, 24 May 2016 15:17:30 GMT

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)

Re: Set Analysis and Custom Table Help Needed

sunny_talwar — Tue, 24 May 2016 15:28:45 GMT

Check now

Re: Set Analysis and Custom Table Help Needed

jcampbell474 — Tue, 24 May 2016 15:47:07 GMT

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.