Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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
Highlighted
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