Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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 ?

Tags (2)
3 Replies
MVP
MVP

Re: How to get the right total with Distinc in expression ?

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

Partner
Partner

Re: How to get the right total with Distinc in expression ?

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
Contributor II

Re: How to get the right total with Distinc in expression ?

Try this.