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

When STK_OPEN vaule = 1259716.26 But Table display as 39051,204 ?

Hi All

Why Table 1 15 Dec display  STK_OPEN = 39,051,204 ( wrong value )

When click on month=12

The STK_OPEN display 1259716.26 ( which is correct )

How to make Table 1 display 1259716.26 for Dec 2015 ?

Paul

1 Solution

Accepted Solutions
p_verkooijen
Partner - Specialist
Partner - Specialist

When I select month 12 in your example.

The SUM of STK_OPEN = 39.051.204

The MAX of STK_OPEN = 1.259.716

When you devide the SUM by MAX it's exact 31 times, you should check your datamodel or use AGGR

View solution in original post

9 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Check the use of . (dot) and , (comma) as numerical value separators. Apparently your document isn't using them in a way that is consistent with the (source) data.

p_verkooijen
Partner - Specialist
Partner - Specialist

When I select month 12 in your example.

The SUM of STK_OPEN = 39.051.204

The MAX of STK_OPEN = 1.259.716

When you devide the SUM by MAX it's exact 31 times, you should check your datamodel or use AGGR

paulyeo11
Master
Master
Author

Hi Sir

Thank you for your sharing.

My expression for STK_CY return correct value , but my STK_LY use below expression return wrong value :-

If(sum(STK_CLOSE) > 0 and sum(STK_OPEN) > 0,

RangeAvg(Only({<year = {$(=max(year)-1)}>}Aggr(Sum({<month, year>}TOTAL <year> If([YearMonth] = YearStart([YearMonth]), STK_OPEN)), [YearMonth], year)),

sum({<month, year, YearMonth = {"$(=Dual(Date(YearEnd(AddYears(Monthstart(Max({<STK_CLOSE = {'*?'}>}YearMonth)), -1)), 'YY MMM'), MonthStart(YearEnd(AddYears(Max({<STK_CLOSE = {'*?'}>}YearMonth), -1)))))"}>}STK_CLOSE)))

when i change sum to Max it does not work , why ?

Paul

paulyeo11
Master
Master
Author

Hi peter

I don't understand what you mean ? can you give me more detail step please .

Paul

Peter_Cammaert
Partner - Champion III
Partner - Champion III

In your OP, your "wrong value" is using comma's, while your "correct value" is using just a single decimal point.

paulyeo11
Master
Master
Author

Hi Peter

what you mean by OP ?

i am not sure where you refer to ? care to paste the script here ?

Thank

Paul

paulyeo11
Master
Master
Author

Hi paul V

I am also paul.

Why sum by max 31 time ?

May I know why the table report sum by max ?

Why I table display Max for Dec ?

Why Jan to Nov not display Max ?

Can you please share with me ?

As this will affect LY stock as Jan 2014 value is also display amount sum by max .

My expression for cal average need to use Jan 2014 Open stock amt , and it will return wrong value.

Paul

Sent from my iPhone

sunny_talwar

What are you expecting to get?

paulyeo11
Master
Master
Author

Hi Sunny

Now my issue is below :-

Why my raw data is 1259716.26 , but when i use sum(STK_OPEN) it display 30,051,204.06 ?

in case you can help me solve the above issue , i think the rest will be okay.

Paul