topic Re: Formula question in QlikView

Formula question

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

I am trying to write a formula that counts certain orders.

Fields : Status Flag, Order Number and Case Number

The status flag tells me what is going on with the case numbers for each order. i am trying to count the number of orders that have case numbers with a status flag of "30" but also has a status flag of "00"

Status Flag

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 15:50:55 GMT

May be this

Count(DISTINCT {<[Order Number] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>} [Order Number])

Re: Formula question

vishsaggi — Tue, 09 Oct 2018 15:56:22 GMT

HI Sunny

can't we just write like this, just wondering

= Count(DISTINCT {< [Status Flag] = {'30', '00'} >} [Order Number])

Re: Formula question

jayanttibhe — Tue, 09 Oct 2018 16:03:20 GMT

Hi Guys,

What about this :

If we need Selections of [Status Flag]to be effective on the Set Expression then (Sunny's expression is the the same - I just simplified - I guess)

Count({< [Order Number]= P({< [Status Flag] = {'30', '00'} >})>}Distinct [Order Number])

If we DONT need Selections of [Status Flag] to be effective on the Set Expression then

Nagaraju your expression

Count(DISTINCT {< [Status Flag] = {'30', '00'} >} [Order Number])

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 16:05:03 GMT

That would include all rows where Status flag is 30 or 00... I am not sure if the OP wants that or if he wants all the Order Numbers where Status Flag is 30 and 00

Re: Formula question

vishsaggi — Tue, 09 Oct 2018 16:07:23 GMT

Got it.

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 16:08:18 GMT

If we need Selections of [Status Flag]to be effective on the Set Expression then (Sunny's expression is the the same - I just simplified - I guess)

Count({< [Order Number]= P({< [Status Flag] = {'30', '00'} >})>}Distinct [Order Number])

I don't think they are the same thing... from what I understand your's will include the Order Number if Status Flag is 30 or 00, where as the one I provided should include only those Order Numbers where the Status Flag is both 30 and 00

Re: Formula question

jayanttibhe — Tue, 09 Oct 2018 16:14:49 GMT

You are right Sunny - mine is OR condition and yours is the Set intersection. My bad

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 16:23:42 GMT

thank you

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 16:24:51 GMT

thank you i wanted all to include both statuses not either or statuses. you formula worked correctly for me.

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 16:55:22 GMT

how do i display this in my expressions to show only these orders for this formula?

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 17:01:12 GMT

Use this set analysis in your expression

{<[Order Number] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>}

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 17:10:02 GMT

I tried that before asking and it errors saying garbage after expression "="

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 17:15:40 GMT

What exact expression did you use?

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 17:22:16 GMT

{<[Order Number] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>}

i copied what you have here i just replaced the field names with the ones i actually have which is this

{<[ORDER_NO] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>}

and it shows this error Garbage after expression: "="

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 17:26:44 GMT

But where is the expression my friend? This is set analysis... but where is Sum or Avg or Min or Max of a field?

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 17:31:05 GMT

i apologize not sure. i am trying to display these orders in a straight table so for the orders expression. i added this expression. i don't want to do any calculations like Sum Avg or Min or Max. i only want to display all other orders that meet this criteria. so all orders that have cartons with a status 00 and 30 within the same order

Re: Formula question

sunny_talwar — Tue, 09 Oct 2018 17:33:18 GMT

You want to show this in a straight table? Without an expression? Okay, may be add a dummy expression like this

Dimension

ORDER_NO

Expression

Count({<[ORDER_NO] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>} ORDER_NO)

and then on the presentation tab, just hide your expression column so that you only see your Dimension. Does that make sense?

Re: Formula question

clsaulsberry — Tue, 09 Oct 2018 17:40:51 GMT

yes sir thank you so much!