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: 
Not applicable

How to get the right total with Distinc in expression ?

Hi everybody , I need your help please.
I have a table with a Budget By Item Family on a specific Month and Year.

Year Month Item Family ItemBudget
201605A00A00-A11100 $
201605A00100-A12100 $
201605A00A00-A13100 $
201605B00B00-B01235 $
201605C00C00-C01100 $

In my Report , I would like to have all Item Family with the Budget , something like this :

Year Month Item Family Budget
201605A00100 $
201605B00235 $
201605C00100 $

to do that I created an expression : Sum(distinct [(Budget)])

the problem is that the total of my expression is 335 $ . This is due to DISCTINCT used in the expression, How can I have the right toal ?

3 Replies
swuehl
MVP
MVP

I think it would be best to separate your budgets values in a table per Year, Month and Item Family (and then use just Sum(Budget) )

If you don't want to change your model,try

=Sum(Aggr( Only(Budget), Year, Month, [Item Family]))

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

This is just a thought. I will look to do something in the script like this

Data:

LOAD Year,

     Month,

     [Item Family],

     Item,

     Left(Budget,3)                                                    AS Budget

FROM

[https://community.qlik.com/thread/216646]

(html, codepage is 1252, embedded labels, table is @1);

DataTrans:

LOAD

    *,

    IF([Item Family] <>Peek([Item Family]) ,1,0)                    AS [Item Family Trans]       

   

    Resident Data;

   

    DROP Table Data;

Then have an Expression like this in the chart

=Sum({<[Item Family Trans]={1}>} Budget)

Hope this helps

anagharao
Creator II
Creator II

Try this.