Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

7 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
paulyeo11
Master
Master
Author

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

paulyeo11
Master
Master
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
paulyeo11
Master
Master
Author

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


Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
paulyeo11
Master
Master
Author

Hi Anil

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

Paul Yeo