Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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))

Life is so rich, and we need to respect to the life !!!
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))

Life is so rich, and we need to respect to the life !!!
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.

Highlighted
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

Life is so rich, and we need to respect to the life !!!
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)))

Life is so rich, and we need to respect to the life !!!
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