Selecting Records that does not exists

[#1]

Hi All,

As I am in the midst of developing a new sheet of data analysis for my work.

Background
2 tables are joined (default: inner join via PID)

1. tblProduct
2. tblTransactions

Current

I am now able to select which Product and which Year is it that I would like to see transactions that have occurred.

New Development

But for this new page, I would still be selecting the Year as a selection criteria, but I would like to see which are the products that has no transaction for the year, which means there would not be any records inside tblTransactions

[Anonymous]

Hi Kar Kit Chong,

The solution that I thought at first is using a Set Analysis expression, something like below:

     = MaxString({1<PNAME ={'*'} - {"=Sum(QTY)>0"}>} PNAME)

A limitation of this solution is that we can't use the expression in a Selection List, but only in a Graphic Object.

I have integrated my solution in the attachment.

I hope this will be helpful.

CE

[#3]

Hi CE BIAN,

I am unable to view your solution..

With your expression am I able to filter out by year too?

e.g. if I am selecting for year 2015, will the SUM(tQuantity) be done for 2015? 

@ce

[jagan]

HI,

Try like this

Dimension: Product

Expression: If(Count(TransactionID) = 0, 'No Transactions In Year')

Regards,

jagan.

[#5]

Hi Jagan,

I am unable to do that, as if there is not transaction for the particular product, there would not be a row in my Transaction Table to count

[jagan]

HI,

Did you tried

Dimension: Product

Expression: If(Alt(Count(TransactionID), 0) = 0, 'No Transactions In Year')

Regards,

jagan.

[Anonymous · Accepted solution]

Hi,

I've made a mistake when I add the attachment, sorry.

Here is my example of the solution, you could filter out by year.

Regards.

CE

[#8]

thank you!! exactly what i needed !! Cheers!

[#9]

hello CE BIAN,

on the other hand, If the conditions are not fulfilled, PNAME will be displayed as "-" is there any way i can hide those rows?