spc a count IF expression

[qliklizzy]

Hi

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

[sunny_talwar]

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

[engishfaque]

Dear Elizabeth,

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

OR

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

Kind regards,

Ishfaque Ahmed

[qliklizzy]

Hi

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

[johnw]

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)

[MarcoWedel]

Hi,

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

thanks

regards

Marco

[qliklizzy]

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

[marcus_sommer]

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

[qliklizzy]

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.

[marcus_sommer · Accepted solution]

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