topic Re: Set Analysis issue in QlikView

Set Analysis issue

kkkumar82 — Mon, 01 May 2017 09:54:11 GMT

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

Re: Set Analysis issue

kkkumar82 — Mon, 01 May 2017 09:54:45 GMT

Its actually giving null values

Re: Set Analysis issue

sunny_talwar — Mon, 01 May 2017 10:36:11 GMT

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

Re: Set Analysis issue

sunny_talwar — Mon, 01 May 2017 10:44:05 GMT

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)')

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

Re: Set Analysis issue

kkkumar82 — Mon, 01 May 2017 12:10:45 GMT

Let me give a try

Re: Set Analysis issue

kkkumar82 — Mon, 01 May 2017 13:14:47 GMT

That worked , thanks sunny

Re: Set Analysis issue

Anonymous — Mon, 01 May 2017 13:27:35 GMT

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

Re: Set Analysis issue

kkkumar82 — Mon, 01 May 2017 13:42:39 GMT

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])))

Re: Set Analysis issue

Anonymous — Mon, 01 May 2017 13:44:29 GMT

... good to know