topic Nested set analysis with max fuction in QlikView

Nested set analysis with max fuction

Mon, 08 Feb 2016 14:56:52 GMT

Hi,

I need to get max date for specific type of data, but I'm having a bit difficulties to get it work when the max function is inside set analysis. I'm trying to get max date only for TYPE Invoice, since type budget is messing up actual max dates. Can someone help me find what the issue with my code:

Sum({< YearMonth = {'>=$($(vCompare2))<=$(=Max({$<TYPE={'Invoice'}>}YearMonth))'}, Year=, MonthName=, Month=, Quarter=, Week= >} $(vGrossSales))

The part {$<TYPE={"Invoice"}>} is what I'm trying to add, but it seems to ignore type of the date, so there must be something wrong with my code?

Re: Nested set analysis with max fuction

sunny_talwar — Mon, 08 Feb 2016 16:34:36 GMT

Try with doube quotes:

Sum({< YearMonth = {">=$($(vCompare2))<=$(=Max({$<TYPE {'Invoice'}>}YearMonth))"}, Year=, MonthName=, Month=, Quarter=, Week= >} $(vGrossSales))

Re: Nested set analysis with max fuction

Mon, 08 Feb 2016 16:51:56 GMT

Unfortunately that doesn't work !either, it now looks following:

Sum({<YearMonth = {">=$($(vCompare2))<=$(=Max({$<TYPE={'Invoice'}>}YearMonth))"},
Year=, MonthName=, Month=, Quarter=, Week= >} $(vGrossSales))

This gives me correct YearMonth, so the problem must be with set analysis:

=Max({<TYPE={'Invoice'}>}YearMonth)

Re: Nested set analysis with max fuction

sunny_talwar — Mon, 08 Feb 2016 17:36:25 GMT

I am thinking its this part of the set analysis which might not be working:

Sum({<YearMonth = {">=$($(vCompare2))"}, Year=, MonthName=, Month=, Quarter=, Week= >} $(vGrossSales))

Can you check if this is working or not?

Re: Nested set analysis with max fuction

Mon, 08 Feb 2016 18:41:58 GMT

Thank you, that was it, taking budgets into account.