Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
paulyeo11
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

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

7 Replies

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

paulyeo11
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.

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

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

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


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

paulyeo11
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

Community Browser