topic Re: spc a count IF expression in QlikView

spc a count IF expression

qliklizzy — Wed, 25 Nov 2020 16:16:04 GMT

Can you carry out an average / SPC / deviation expressions on a count if expression?

I have this formula which flags whether that data item was a cancellation or not, and then I can count all the 1's to show this against the activity.

count(if(match(CancelledOnDay,'1'),'1') )

However I want now add a SPC due to some process changes to see whether it will impact on cancellations and improve on activity

I have followed the instruction I found on here on how to do SPC in Qlikview but as my field isn't a fields its a count if expression - I cant seem to get it to work>?

Is there any way I can?

thanks in advance for taking a look

Liz x

Re: spc a count IF expression

sunny_talwar — Fri, 08 Apr 2016 16:39:30 GMT

Not sure what SPC is, can elaborate a little? and if possible provide a sample?

Re: spc a count IF expression

engishfaque — Fri, 08 Apr 2016 17:07:30 GMT

Dear Elizabeth,

=Count (if(CancelledOnDay = 'SPC', 1))

=Count (if(YourFieldName = 'SPC' and CancelledOnDay = 1, 1))

Kind regards,

Ishfaque Ahmed

Re: spc a count IF expression

qliklizzy — Fri, 08 Apr 2016 19:15:29 GMT

It is a statistical process, with upper and control limits based on the standard deviation of the mean/average

I do it in excel all the time but trying to do it in Qlikview is proving difficult.

I have seen a tutorial which looks simple, however they already had a field used for the calculation on the mean , standard deviation and control limits, I have a calculated field counting text.

Thanks

Sent from my iPhone

Re: spc a count IF expression

johnw — Fri, 08 Apr 2016 23:16:59 GMT

Unfamiliar with SPC as well, but if the issue is that this isn't a field, can you just make it a field during the load?

,if(CancelledOnDay='1',1) as CancelledOnDay1

And as a more general observation perhaps not relevant to this specific question, try to avoid count(if(...)), or sum(if(...)), or any such thing. If you can put the condition in the script to make a field that lets you do a straight count() or sum(), that's ideal, at least for speed which I tend to be more concerned with than space. If not in the script, use set analysis.

count({<CancelledOnDay={'1'}>} SomeField)

Re: spc a count IF expression

MarcoWedel — Sat, 09 Apr 2016 00:41:49 GMT

Hi,

can you please post an example of what you're doing in Excel that you want to rebuild in QlikView?

thanks

regards

Marco

Re: spc a count IF expression

qliklizzy — Mon, 18 Apr 2016 08:26:46 GMT

thank you for your replies sorry for my delay I had to take leave for a week.

this is what want to achieve that is covered here: https://community.qlik.com/docs/DOC-4245

but my count is based on this count(if(match(CancelledOnDay,'1'),'1') ) which is an expression on a field to give me a numbered count of activity, but in the document in the link they already have a field as a count.

I cant get this to work when trying to get an average, or standard deviation

thanks

Re: spc a count IF expression

marcus_sommer — Mon, 18 Apr 2016 13:34:46 GMT

I think in this case you will need an adavanced aggregation with aggr which wrapped your expression, maybe something like this:

median(aggr(count(if(match(CancelledOnDay,'1'),'1') )), YourDimensions))

This creates a virtual table with your dimension(s) and your count-expression and on this table you applied your statistical expressions, see also: AGGR...

- Marcus

Re: spc a count IF expression

qliklizzy — Mon, 18 Apr 2016 14:12:57 GMT

thanks for reply...

I have tried some AGGR expressions as I thought this was the way to go also but I'm still confused and cannot get it to work.

in my script this is the field I am using: CxldReason, if(Cxld='Y',1,0) as CancelledOnDay,

I have tried:

=avg(aggr(sum((CancelledOnDay)),Month))

=median(aggr(sum(CancelledOnDay) , Month))

but they both show the same result as the count, and not showing as a straight line average. I will then want to use this field to work out my upper and lower limits.

Re: spc a count IF expression

marcus_sommer — Mon, 18 Apr 2016 14:48:57 GMT

You migt need to change the used dimension for example to year/week/day and/or to use a NODISDINCT within the aggr() and/or a TOTAL within the outer aggregation, also maybe something like this:

median(TOTAL aggr(NODISTINCT count(if(match(CancelledOnDay,'1'),'1') )), Day))

- Marcus

Re: spc a count IF expression

qliklizzy — Mon, 18 Apr 2016 15:35:42 GMT

This works! thanks so much

I have done it using weekyear dimension as it was a bit much per day - definitely done the job!

now to work on st dev, and upper and lower limits.

I'm made up, thanks

Liz x