# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Valued Contributor II

## How to make my table display 166K instead of 110K ?

Hi All

I have a Table. it give the correct value. which i select only 1 SOURCE

For example SOURC = ID

But when i want to view the report by all SOURC , it return the wrong value.

Paul Yeo

.

1 Solution

Accepted Solutions
MVP

## Re: How to make my table display 166K instead of 110K ?

Perhaps this?

Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK))), SOURCE)),\$(vMoneyFormatK))

Life is so rich, and we need to respect to the life !!!
7 Replies
MVP

## Re: How to make my table display 166K instead of 110K ?

Perhaps this?

Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK))), SOURCE)),\$(vMoneyFormatK))

Life is so rich, and we need to respect to the life !!!
Valued Contributor II

## Re: How to make my table display 166K instead of 110K ?

wow , you are scripting expert. Thank you very much. it help me a lot.

Highlighted
Valued Contributor II

## Re: How to make my table display 166K instead of 110K ?

Hi Sir

I apply your script into my table 1 , it work fine for SOURCE=ID.

Now i try to change SOURCE = PG , it does not work.

Can you pls help me add another condition ?

As i have try it does not work :-

Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(Sum(Aggr(If(Match(SOURCE,'TDSPG'),money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK))), SOURCE)),\$(vMoneyFormatK))

MVP

## Re: How to make my table display 166K instead of 110K ?

currently, I don't have app in hand. So i can't test. Perhaps this as assumption?

Pick(Match(SOURCE, 'TDSID', 'TDSPG')+1,

Money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)))

I setup the colors on each to better understand For Others    For TDSID     For TDSPG

Life is so rich, and we need to respect to the life !!!
Valued Contributor II

## Re: How to make my table display 166K instead of 110K ?

Hi Anil,

Wow such a complicated script , you can make it work with out testing . I try to add one more SOURCE = TDSKL , it does not work , where i go wrong ?

Pick(Match(SOURCE, 'TDSID', 'TDSPG', 'TDSKL')+1,

Money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSKL'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK))))

MVP

## Re: How to make my table display 166K instead of 110K ?

Because, you are varying it to each from different sector to different matching. So, How Pick(Match(...)) functionality works from Left-Right. In your way, you are having different name and you are calculating with different Manner. For a while try this?

Pick(Match(SOURCE, 'TDSID', 'TDSPG', 'TDSKL')+1,

Money(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}>}Amount)/\$(Columndim89)*1/1000,\$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)),

Money(RangeSum(Above(Sum({<\$(ColumnDim901)={\$(ColumnDim901)}, SOURCE = {'TDSKL'}>}Amount)/\$(Columndim89)*1/1000, 0, Rowno(TOTAL))), \$(vMoneyFormatK)))

Life is so rich, and we need to respect to the life !!!
Valued Contributor II

## Re: How to make my table display 166K instead of 110K ?

Hi Anil

Your last expression , you make it very easy for me to add new field like TDSKL. Thank you very much.

Paul Yeo