Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to write expression if I want to display total value per BRAND without see click Quarter or Month if Quarter i appear as Dimension?

How to write expression if I want to display TOTAL value per BRAND without see click Quarter or Month if Quarter i appear as Dimension?

I show BRANCH, YEAR, and QUARTER as dimension on straight table. I don't show MONTH as dimension.

For the example :

if i selected a list box (this dimension is MONTH). I just want to get the TOTAL value of Q, Q2, Q3, Q4 per BRAND. So Q1, Q2, Q3, and Q4 have same value as TOTAL (without see Quarter, only TOTAL value per BRAND). In my example, expression TOTAL ACTUAL.

If i selected month, this total value is not included in that quarter of selected month, always NULL. It must show total value per year. I want that TOTAL value can't change.

This is my expression :

Aggr(NODISTINCT Sum({$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'},

//TRX_DATE_TRX={">=$(=YearStart(MakeDate(Max(YEAR_TRX),1,1)))<=$(=YearEnd(MakeDate(Max(YEAR_TRX),12,31)))"},

MONTH_TRX=, DAY_TRX=>}

$(vActual)), GRUP_BRAND_DESC, BRAND)

Can someone help me please if i click MONTH or DAY or QUARTER field, this TOTAL value will not change? I just want to get the Total Actual Sales per BRAND into expression TOTAL SALES

Thanks.

Danny H

1 Solution

Accepted Solutions
Not applicable
Author

I can use this code:

Sum(TOTAL <GRUP_BRAND_DESC, BRAND> {$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'},

MONTH_TRX=, DAY_TRX=

>} $(vActual))

View solution in original post

5 Replies
adhudson
Creator II
Creator II

Hi,

     If I am not wrong, you are using a variable called 'vActual' in sum function.

     But after looking into the variable overview, I found that, you have given some if conditions and the variable wil only return string value but not any numeric.

     As you know, basically it is not possible to sum the string values. Hence the sum function returns 0.

     Any zero value cannot be used in aggr function. thats why you are getting any results.

     so you should give the correct values in sum function to get the result.

Regards

Andrew Hudson

Not applicable
Author

this is vActual :

= if(SATUAN = 'KARTON',

if(CATEGORY_SALES = 'Gross Sales', 'KTN_GROSS_ACTUAL',

if(CATEGORY_SALES = 'Return Sales', 'KTN_RETURN_ACTUAL',

if(CATEGORY_SALES = 'Net Sales', 'KTN_NETTO_ACTUAL'

)))

,

if(SATUAN = 'KG',

if(CATEGORY_SALES = 'Gross Sales', 'KG_GROSS_ACTUAL',

if(CATEGORY_SALES = 'Return Sales', 'KG_RETURN_ACTUAL',

if(CATEGORY_SALES = 'Net Sales', 'KG_NETTO_ACTUAL'

)))

,

if(SATUAN = 'VALUE' and TAX = 'Excluded',

if(CATEGORY_SALES = 'Gross Sales', 'AMT_GROSS_ACTUAL',

if(CATEGORY_SALES = 'Return Sales', 'AMT_RETURN_ACTUAL',

if(CATEGORY_SALES = 'Discount Sales', 'AMT_DISCOUNT_ACTUAL',

if(CATEGORY_SALES = 'Net Sales', 'AMT_NETTO_ACTUAL'

))))

,

if(SATUAN = 'VALUE' and TAX = 'Included',

if(CATEGORY_SALES = 'Gross Sales', 'AMT_GROSS_TAX_ACTUAL',

if(CATEGORY_SALES = 'Return Sales', 'AMT_RETURN_TAX_ACTUAL',

if(CATEGORY_SALES = 'Discount Sales', 'AMT_DISCOUNT_TAX_ACTUAL',

if(CATEGORY_SALES = 'Net Sales', 'AMT_NETTO_TAX_ACTUAL'

))))

))))

This is only return 1 field to get the value.

adhudson
Creator II
Creator II

Hi,

     I know that it returns only one value.

     But how can you sum the string values other than numeric values. That is what I am asking.

Regards

Andrew Hudson

Not applicable
Author

I don't use variable vActual in this expression :

Aggr(NODISTINCT Sum({$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'},

MONTH_TRX=, DAY_TRX=

>} KG_NETTO_ACTUAL), GRUP_BRAND_DESC, BRAND)

The value of KG_NETTO_ACTUAL always numeric.

Why when i selected Quarter Q2, Q1 always Null? Or i selected Month Mey, Q1 is null and Q2 display right value.

I want Q1 value is the same of Q2.

May be someone know to help me solve this problem.

Thanks

Not applicable
Author

I can use this code:

Sum(TOTAL <GRUP_BRAND_DESC, BRAND> {$<ACTUAL_FLAG = {'*'} - {'0'}, ORG_ID = {'*'} - {'84'},

MONTH_TRX=, DAY_TRX=

>} $(vActual))