Set Analysis issue

[kkkumar82]

Hi All

My below set analysis is not working properly after adding Modifiers

Pick(Dim,num(Sum({<Employee={"=AGGR(Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4), Employee)<=25"},[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),'$#,##0.00;($#,##0.00)')

,

num(Sum({<Employee={"=AGGR(Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4), Employee)<=25"},[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),'$#,##0.00;($#,##0.00)')

,

Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT])))

it was working fine when it was like below, I guess the double quotes is causing the problem but not sure how to handle it

Pick(Dim,num(Sum({<Employee={"=AGGR(Rank(Sum({<[per.PERIOD_YEAR] = {$(vSelectedYear)}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4), Employee)<=25"},[per.PERIOD_YEAR] = {"$(vSelectedYear)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),'$#,##0.00;($#,##0.00)')

,

num(Sum({<Employee={"=AGGR(Rank(Sum({<[per.PERIOD_YEAR] = {$(vSelectedYear)}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4), Employee)<=25"},[per.PERIOD_YEAR] = {"$(vSelectedYear)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]),'$#,##0.00;($#,##0.00)')

,

Rank(Sum({<[per.PERIOD_YEAR] = {$(vSelectedYear)}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT])))

Any light on this

[kkkumar82]

Its actually giving null values

[sunny_talwar]

May be try changing the inner double quotes to single quotes?

[sunny_talwar · Accepted solution]

Like this

Pick(Dim,

Num(Sum({<Employee = {"=Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={'<=$(=vClosedFisPerSortKey)'}>} [Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4) <= 25"}, [per.PERIOD_YEAR] = {$(=vCurrentYear)}, ClosedFisPer_Sort_key = {"<=$(=vClosedFisPerSortKey)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]), '$#,##0.00;($#,##0.00)')

,

Num(Sum({<Employee = {"=Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={'<=$(=vClosedFisPerSortKey)'}>} [Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4) <= 25"}, [per.PERIOD_YEAR] = {$(=vCurrentYear)}, ClosedFisPer_Sort_key = {"<=$(=vClosedFisPerSortKey)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]), '$#,##0.00;($#,##0.00)')

,

Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT])))

[kkkumar82]

Let me give a try

[kkkumar82]

That worked , thanks sunny

[Anonymous]

so you have to alternate single and  double quotes for distinguishing the "quote areas"?

[kkkumar82]

Yes, the suggestion was to change the double quotes to single quotes for the highlighted area and it worked

Pick(Dim,

Num(Sum({<Employee = {"=Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={'<=$(=vClosedFisPerSortKey)'}>} [Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4) <= 25"}, [per.PERIOD_YEAR] = {$(=vCurrentYear)}, ClosedFisPer_Sort_key = {"<=$(=vClosedFisPerSortKey)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]), '$#,##0.00;($#,##0.00)')

,

Num(Sum({<Employee = {"=Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={'<=$(=vClosedFisPerSortKey)'}>} [Concur.REPORT_ENTRY_APPROVED_AMOUNT]),4) <= 25"}, [per.PERIOD_YEAR] = {$(=vCurrentYear)}, ClosedFisPer_Sort_key = {"<=$(=vClosedFisPerSortKey)"}>}

[Concur.REPORT_ENTRY_APPROVED_AMOUNT]), '$#,##0.00;($#,##0.00)')

,

Rank(Sum({<[per.PERIOD_YEAR]={$(=vCurrentYear)},ClosedFisPer_Sort_key={"<=$(=vClosedFisPerSortKey)"}>}[Concur.REPORT_ENTRY_APPROVED_AMOUNT])))

[Anonymous]

... good to know