topic Re: Doing Sum in text box with set analysis in QlikView

Doing Sum in text box with set analysis

alec1982 — Fri, 25 Apr 2014 17:52:02 GMT

Hi all,

I am trying to get a text box to show count of distinct Sales Orders where the bill date is within a period of time.

Sales Order Bill Date Period

1 03-03-2014 201403

2 01-03-2014 201403

3 04-05-2012 201403

4 01-01-2011 201312

I need a text box to show me the count of the distinct sales Order by Period where the bill date is within this period. so if the period is 201403 then I need the count where the bill date is within March 2014.

Your help is appreciated..

Thxs

Re: Doing Sum in text box with set analysis

its_anandrjs — Fri, 25 Apr 2014 18:33:18 GMT

Write like below in the text box

=Count(DISTINCT {<Period = {'201403' }> } [Sales Order] )

Do you have month field also if not then add month field like

Month([Bill Date]) as Month then use below script

=Count(DISTINCT {< Period = {'201403'}, Month = {'Mar'} >} [Sales Order] )

Re: Doing Sum in text box with set analysis

Anonymous — Fri, 25 Apr 2014 19:08:14 GMT

See attached. Is this what you need?

Re: Doing Sum in text box with set analysis

Fri, 25 Apr 2014 19:10:19 GMT

Badr,

has your period always 'YYYYMM' format?

if yes, you need something like this ....

Let me know if it helps.

And take care about date formats

regards

Darek

Re: Doing Sum in text box with set analysis

alec1982 — Fri, 25 Apr 2014 19:24:11 GMT

Hi,

This wrong as it will include Sales Order Nr3 which is not in the right period.

Thxs

Re: Doing Sum in text box with set analysis

its_anandrjs — Fri, 25 Apr 2014 21:02:31 GMT

Load your table like below script and introduce Month field on the table

LOAD *, Date#([Bill Date],'DD-MM-YYYY') AS [Billing Date];

LOAD *, Month(Date#([Bill Date],'DD-MM-YYYY')) as Month; // This field is from the Bill Date.

LOAD * INLINE

[

Sales Order,Bill Date, Period

1, 03-03-2014,201403

2, 01-03-2014,201403

3, 04-05-2012,201403

4, 01-01-2011,201312

];

NoConcatenate

New:

LOAD

[Bill Date],

[Sales Order],

Period,

Month

Resident T;

DROP Table T;

Other wise you can add the Flag field on this also

And on the Text object write

=Count(DISTINCT {< Period = {'201403'}, Month = {'Mar'} >} [Sales Order])

And it gives output like

Regards

Re: Doing Sum in text box with set analysis

alec1982 — Sun, 27 Apr 2014 15:24:59 GMT

Thank you!