Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
kkkumar82
Specialist III
Specialist III

Set Analysis issue

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
kkkumar82
Specialist III
Specialist III
Author

Its actually giving null values

sunny_talwar

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

sunny_talwar

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
Specialist III
Specialist III
Author

Let me give a try

kkkumar82
Specialist III
Specialist III
Author

That worked , thanks sunny

Anonymous
Not applicable

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

kkkumar82
Specialist III
Specialist III
Author

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
Not applicable

... good to know