topic Re: Straight table combining expressions in QlikView

Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 18:30:10 GMT

I have a straight table with one Dimension, Name and two expressions, # of Purchases and YTD Amount. The result set relies on the expressions working together, meaning, I need to only see the total # of Purchases that are greater than 10 and where the YTD Amount is greater than 10,000. How can I write the two expressions?

Re: Straight table combining expressions

trdandamudi — Thu, 21 Jul 2016 18:44:51 GMT

Give a try on the below:

Sum({<[# Of Purchases]={"$(=Count([# Of Purchases])>10"},[YTD Amount] = {'>10,000'}>} [# Of Purchases])

If this is not helping, can you please post a small sample.

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 18:47:16 GMT

That is only one expression. Is the YTD Amount expression just a straight forward Sum?

Re: Straight table combining expressions

maheshkuttappa — Thu, 21 Jul 2016 18:51:29 GMT

Post your sample data and your expression

Re: Straight table combining expressions

trdandamudi — Thu, 21 Jul 2016 18:51:40 GMT

The expression I gave you have the both conditions:

It will sum [# Of Purchases] only if count of [# Of Purchases] are greater than 10 and [YTD Amount] >10,000

Is that is what you are looking for ?

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 18:53:42 GMT

No, the table will have 3 columns. Name, # of Purchases, YTD Amount.

I only want to see the # of Purchases that are greater than 10 and the YTD Amount is greater than 10,000.

So, I believe I will need two expressions.

Re: Straight table combining expressions

trdandamudi — Thu, 21 Jul 2016 18:57:06 GMT

Try as below:

Dimension: Name

Expression1:

Count({<[# Of Purchases]={"$(=Count([# Of Purchases])>10"},[YTD Amount] = {'>10,000'}>} [# Of Purchases])

Expression2:

Sum(YTD Amount)

Hope this helps...

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 19:06:49 GMT

[YTD Amount] is not a field that exists...it is an expression.

Re: Straight table combining expressions

karthikoffi27se — Thu, 21 Jul 2016 19:12:39 GMT

Hi Cliff

First expression goes like this

Sum({<[# Of Purchases]={"$(=Count([# Of Purchases])>10"}>} [# Of Purchases])

And second one

Sum({<[YTD Amount] = {'>10,000'}>}[YTD Amount])

Thanks

Karthik

Re: Straight table combining expressions

trdandamudi — Thu, 21 Jul 2016 19:21:13 GMT

Can you please share a small sample ....

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 19:26:11 GMT

In this example, I only want to see the results for Dave Donaldson and Tom Thompson.

Re: Straight table combining expressions

sunny_talwar — Thu, 21 Jul 2016 19:29:40 GMT

What is your expression for YTD Amount here?

May be like this:

Count({<Name = {"=Count([# Of Purchases]) >10 and Expression for YTD Amount >10000"}>} [# Of Purchases])

Replace Expression for YTD Amount with your expression you have used for YTD Amount.

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 19:38:36 GMT

That is part of the issue. I don't know what the expression for YTD Amount should look like...

Re: Straight table combining expressions

trdandamudi — Thu, 21 Jul 2016 19:39:46 GMT

Please take a look at the attached:

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 19:43:58 GMT

The # of Charges needs to be a Sum and the expression for YTD Amount only works with the small subset of data. There needs to be a check for the dollar amount as well as the total # of charges.

Re: Straight table combining expressions

trdandamudi — Thu, 21 Jul 2016 20:12:47 GMT

Please see the attached:

Re: Straight table combining expressions

cliff_clayman — Thu, 21 Jul 2016 21:01:39 GMT

None of these solutions seem to fit. Is there a way to use Calculated Dimensions to make this easier?

The issue is that I do not know what the expressions should be. I have 3 fields. Name, Charges, Amount. I have multiple names with multiple charges and amounts. I need an expression that will Sum the vales for Charges and Amounts. I then need to filter the results for charges that are greater than 10 AND where the amounts are greater than $10,000. I don't know if it would be easier to create a calculated dimension that does the check for the condition and suppress null values and then just do the Sum of the charges and amounts in an expression.

Re: Straight table combining expressions

karthikoffi27se — Fri, 22 Jul 2016 04:08:09 GMT

Can you let us know the expression that you have used in the excel got YTD Amount..

Re: Straight table combining expressions

cliff_clayman — Fri, 22 Jul 2016 14:43:51 GMT