Count Distinct based on a field based on a Measure

[JawakarShyam]

Hi,

Can you kindly help me understand how to apply distinct count for the following expressions,

count(distinct{<[Price check]={"=If((sum([Dealer Sell-in price])-[Unit Price (EUR)])>0,'N','Y')"}>}[Part Number])

In the above expression,  , Unit Price = sum(Price * Exchange
)/sum(Quantity), Price check=If((sum([Dealer Sell-in price])-([Unit Price (EUR)]))>0,'N','Y'))

Currently, the formula is making distinct count on all the part number but I need it distinct count only the [Part Number] with [Price check] is 'Y' for each Dealer.

Let me know if you need any other details from me.

Thanks in advance

[Lech_Miszkiewicz]

How about just:

count(distinct{<[Price check]={"=sum([Dealer Sell-in price]-[Unit Price (EUR)])<=0"}>}[Part Number])

[JawakarShyam]

Hi ,

Thank you for the suggestion but unfortunately it's still showing all the unique part number. Not only the price check with 'Y'.

Thanks,

Jawa

[BrunPierre · Accepted solution]

The If condition in your expression is redundant because it's evaluating a condition that already exists as the [Price check] field.

So, the expression can be simplified to:

count({< [Price check]={'Y'} >} distinct [Part Number])

[JawakarShyam]

Hi, I've tried it before but as [Price check] is a measure it doesn't seem to work.

Thanks,

[Lech_Miszkiewicz]

It is very unclear how your model is built. You say that [Price check] is a measure - do you mean it is master item measure created on UI? If that is the case then it is poor practice to start with as it should be moved to script and then it could be easily used as per @BrunPierre suggestion. If not you may need to give us more info about your model / tables / data association so we could picture it. 

cheers

[JawakarShyam]

Yes, [Price check] is a master item measure created on the UI. I'm relatively new to the development of Qliksense from the backend and I'm building some components on top of an existing Qliksense app but since there are many sections, i'm not able to create the formula directly as the components are spread out in different sections. If there is no direct way to use the Master measure created from the UI. I'll try to create the formula in the backend in the script. 

I'll try to upload a model for better understanding.

thanks.

[JawakarShyam]

Hi,

Thank you, after creating the Price check in the load editor.

The Expression seems to be working. 

Thanks,

Jawakar