topic Re: need help in QlikView

need help

Tue, 07 Jan 2014 10:46:46 GMT

hi, how can i write this in chart expression?

SELECT (amQty) FROM FCTRAN WHERE substri(FCTRANSIT,1,1)='S' and substri(FCTRANSIT,1,1)='M'

Re: need help

vikasmahajan — Tue, 07 Jan 2014 10:59:34 GMT

better while loading make flag S & M

and use in set analysis like following

sum( {$< Fiscal Year={$(=max(FiscalYear))}, flag={'S'} amQty )

Hope this helps you.

Vikas

Re: need help

Tue, 07 Jan 2014 11:05:10 GMT

hi vikas,

it s not clear. pls explain. thanks

Re: need help

vikasmahajan — Tue, 07 Jan 2014 11:12:50 GMT

Dear Dushan,

In sql script only while taking data from sql create flag S or M

load this in qlikview

apply set anlysis sum( {$< flag={'S'} amQty )

This way you can achieve the same.

Vikas

Re: need help

jagan — Tue, 07 Jan 2014 11:20:17 GMT

Hi,

Try like this

=Sum({<FCTRANSIT={'S*', 'M*'}>}amQty)

Regards,

Jagan.

Re: need help

Tue, 07 Jan 2014 11:35:40 GMT

thanks jagan. it s working. but my full query is like this. Can i do that?

=Sum({<site={'S*', 'M*','SAM*','SML*','MBO*'}>}amQty)

and also i want to get this data in MONTH='11'

help pls

Re: need help

Tue, 07 Jan 2014 11:39:02 GMT

Hi Dushan ,

Sum ( {<Mid(FCTRANSIT,1,1) ={'S' , 'M'}>} amQty)

It works ..

Regards,

Koti reddy

Re: need help

manideep78 — Tue, 07 Jan 2014 11:42:34 GMT

Include Month in same expression

=Sum({<site={'S*', 'M*','SAM*','SML*','MBO*'}, Month={11}>}amQty)

Re: need help

Tue, 07 Jan 2014 12:09:16 GMT

Hi Manideep, I applied it but got the same answer without month filtering. why is that?

Re: need help

Tue, 07 Jan 2014 12:19:09 GMT

=Sum({<site={'S*', 'M*','SAM*','SML*','MBO*'}>}amQty)

=Sum({<site={'S*', 'M*'}>}amQty) Returns same Values

Becoz S* results SAM* ,SML* also

for Second Check Whether There Is A relation between

.. Check out Whether there is a Site and Month ..

If there is relation then it will not Return Same for =Sum({<site={'S*', 'M*','SAM*','SML*','MBO*'}, Month={11}>}amQty)

and above..

If there is no relation it will return same..

Re: need help

Tue, 07 Jan 2014 12:30:41 GMT

sorry ppl,

'SAM','SML' and 'MBO' should be not equal. it s like this

SELECT (amQty) FROM FCTRAN WHERE substri(site,1,1)='S' and substri(site,1,1)='M' and substri(site,1,1) != 'SAM' and substri(site,1,1) != 'SML' and substri(site,1,1) != 'MBO'

Re: need help

manideep78 — Tue, 07 Jan 2014 12:41:00 GMT

try this

=Sum({<site={'S*', 'M*'} - {'SAM*','SML*','MBO*'}, Month={11}>}amQty)

Note: Make sure your month field should be in standard date format, other wise change it using relevant date functions.

Re: need help

Tue, 07 Jan 2014 12:44:24 GMT

You can Use this one

Sum({<site={'S*', 'M*'} - {'SAM*','SML*','MBO*'}, Month={11}>}amQty)

It helps.

Re: need help

jagan — Wed, 08 Jan 2014 02:52:50 GMT

Hi,

Try this expression.

Sum({<site={'S*', 'M*'} - {'SAM*','SML*','MBO*'}, Month={11}>}amQty)

Regards,

Jagan.