Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

egoziyan
New Contributor II

Sum total issue

G'day,

i need your advice with an issue I'm dealing with.

I want to combine the number of invoices into group of sales (group 1 - number of invoices between 0 - 200 $, group 2 number of invoices between 201 - 300 , group 3  number of invoices abouve $300)

My data is:

store, worker, invoice_no, sa_total_without_tax, month

11,jhon, 10055,170,3

11,jhon,10056,180,3

11,jhon,10057,280,3

11,jhon,10058,400,3

11,Peter,10059,180,3

11,Peter,10060,200,3

11,Peter,10061,250,3

the result should be like

MonthWorkerGroup1Group2Group3
3jhon211
3Peter120

My problem is that I am getting all the invoices into group 3 when I am droping the invoice_no dimention.

my expression is

Group1 -

(sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 0 and sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) < 200 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

Group2 -

(sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 200 and sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) < 300 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

Group3 -

(sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 300 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

Thanks in advance

Yaniv

1 Reply
MVP & Luminary
MVP & Luminary

Re: Sum total issue

Create the groups in the script instead:

LOAD

    store,

    worker,

    invoice_no,

    sa_total_without_tax,

    month,

    if(sa_total_without_tax<200,'Group1',

        if(sa_total_without_tax<300,'Group2','Group3')) as Group

FROM ...somesource...;


talk is cheap, supply exceeds demand