Nested set analysis

[schmidtkm37]

Hi all,

I am trying to create a nested set expression. I want to return the sum of sales for fiscal year to date for the prior fiscal year in order to do fiscal year over year calculations. My fiscal year starts in October so for 12/19/18, I want sum of sales for 10/1/17 - 12/19/17. I'm trying to avoid any hard coding so that these dashboards don't all break once the calendar rolls over to October again next year.

The following expression works to get the start date of the previous fiscal year:

        min({$<FiscalYear={"$(=max(FiscalYear)-1)"}>}vDate)

And I set a variable in the script to return the date from a year ago (today it returns 12/19/17) called vDateYearAgo

How can I use a set expression to return the sum of sales where vDate <= DateYearAgo and  >=  min({$<FiscalYear={"$(=max(FiscalYear)-1)"}>}vDate)?

I tried to define the min({$<FiscalYear={"$... variable in the script but it doesn't seem to work. And I thought about defining a dimension in Master Items and use that in a formula, but we can't reference Master Items in formulas. Any ideas? Also, I'm aware that I might not be going about this in the right way, so any thoughts on a better strategy are certainly appreciated.

Thanks

[sunny_talwar]

What is vDate here? Is this a variable or field?

[schmidtkm37]

It is a field. It's the date of the invoice. I'm not sure why it has a "v". I didn't name it.

[sunny_talwar]

May be try something like this

Sum({<vDate = {"$(='>=' & Date(YearStart(Max(vDate), -1, 10), 'MM/DD/YY') & '<=' & Date(AddYears(Max(vDate), -1), 'MM/DD/YY'))"}>} Sales)

[CarlosAMonroy]

Hi,

If you already have Fiscal Calendar and fields, just apply the following logic to your set analysis:

Prior YTD
{<DateField={">=$(=min({$<FiscalYear={"$(=max(FiscalYear)-1)"}>}vDate)) <=$(=AddMonths( today(),-12))"}>}

I guess YTD should be the same start variable but removing the '-1'.

YTD
{<DateField={">=$(=min({$<FiscalYear={"$(=max(FiscalYear))"}>}vDate)) <=$(= today())"}>}

Hope that helps,
Carlos

[schmidtkm37]

Thanks for the quick response Sunny, the issue here is that the date table that joins to the invoice table has dates going out to 2040, so the first function comes back as 10/1/2039 instead of 10/1/2018. 

Also, our fiscal year is weird in that it actually starts on the Monday of the week that includes Oct 1, so the best example to see that would be 2016 when it started on 9/26. That's why I used:

min({$<FiscalYear={"$(=max(FiscalYear)-1)"}>}vDate)

to get the start of the fiscal year.

[schmidtkm37]

Carlos, thank you for responsding. It doesn't like the two sets of " and also doesn't recognize DateField (should this be vDate?)

[CarlosAMonroy]

Right, should be your date field, not sure if its Fiscal year or vDate. Qlikview may underline it in red as a syntax error. But that should work if you're using the correct fields.

Carlos M

[sunny_talwar]

In that case try using [] instead of " " within the inner set analysis

Sum({<vDate = {"$(='>=' & Date(Min({$<FiscalYear = {[$(=Max(FiscalYear)-1)]}>} vDate), 'MM/DD/YY') & '<=' & Date(DateYearAgo, 'MM/DD/YY'))"}>} Sales)

or use Escape sequences

Sum({<vDate = {"$(='>=' & Date(Min({$<FiscalYear = {""$(=Max(FiscalYear)-1)""}>} vDate), 'MM/DD/YY') & '<=' & Date(DateYearAgo, 'MM/DD/YY'))"}>} Sales)

[schmidtkm37 · Accepted solution]

After some trial and error, I have found the solution. Thanks to Sunny and Carlos for being so responsive and pointing me in the right direction. 

sum({<[vDate]={"=[vDate]<=vDateYearAgo"},
  FiscalYear={"$(=max(FiscalYear)-1)"}
  >}  InvoiceAmt)