topic Help with Set Analysis Expression... in QlikView

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 12:04:30 GMT

Hi, i am trying to write an expression to bring back total SalesValue - Total Voids for a transaction.

I know how to calculate total sales value - Sum(SalesValue), but this also includes VOID values. A void is obviously not part of the transaction value, so i need to deduct this off.

my fields are

SalesValue (the price of each item)

LineType (specifies if the transaction line is a SALE/ VOID/REFUND etc)

I have tried the following....

Sum({$<LineType ='V'>}SalesValue)

but this just displays the void value.

Can anyone help?

Help with Set Analysis Expression...

jonathandienst — Tue, 14 Sep 2010 12:12:57 GMT

This might do what you want:


Sum({$<LineType -= {'V'}>}SalesValue)

Jonathan

Help with Set Analysis Expression...

Tue, 14 Sep 2010 12:16:07 GMT

shouldn't be somethig like

Sum({$<~LineType = {'V'}>}SalesValue)
?

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 12:18:04 GMT

Thanks Jonathan,

I have already tried that one, but i think i know why it doesn't work....

I need the expression to disregard the selection (TransactionType).

How would i include this?

Help with Set Analysis Expression...

jonathandienst — Tue, 14 Sep 2010 12:24:45 GMT

You did not mention TransactionType in the original post, so I am not sure what you mean.

Jonathan

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 12:27:27 GMT

I need to show the total SalesValue (but exclude the Transaction Type selection) minus Voids.

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 13:22:01 GMT

Any more help with this anyone?

Help with Set Analysis Expression...

Tue, 14 Sep 2010 13:30:24 GMT

Hi,

The transaction Type is another field or is the same LineType?

Regards.

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 13:37:54 GMT

It is another field. i want anything from this selection excluded.

Help with Set Analysis Expression...

Tue, 14 Sep 2010 13:39:10 GMT

Hi , I think this expression can help you.

Because you want to use not like condition

here is one expression you can do desired changes in this set analysis expression and can get desired result......

= (-count({$<YM={'$(vCurrentyearmonth)'},Year=,Quarter=,MonthName=,BDO_DSA={'DSA'},Source={'SanctionNo'},BDO_NAME={'CONN*'}>} DISTINCT FILENO)

Regards,

Dushyant

Help with Set Analysis Expression...

Miguel_Angel_Baeyens — Tue, 14 Sep 2010 13:41:33 GMT

Hello,

Use

Sum({< LineType -= {'V'}, TransactionType = >} SalesValue)

to ignore the current selection in TransactionType.

Hope that helps

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 13:45:43 GMT

Excellent, that worked.

Thanks very much!

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 13:54:03 GMT

Hi, That worked to show me the result i wanted, but....

now it removes the Transaction Type selection.

So if i select a Transaction Type of 'Account Sales', the results show me all sales for the selected dates of ACCOUNT SALES. When i click on a transaction this show's me the details ogf that particular transaction but it removes 'Account Sales' from my Transaction Type selection. so when i click back i am expecting to see ACCOUNT SALES again, but instead it show's me everything.

This was working until i added the expression given earlier.

Any ideas?

Ignore the above......

It looks like the expression given earlier actually ignores the 'Transaction Type' selection completely.

I do not want that to happen. I still need to filter on 'Transaction Type' but i do not want it in the SalesValue calculation..

Is this possible?

Help with Set Analysis Expression...

Miguel_Angel_Baeyens — Tue, 14 Sep 2010 14:44:54 GMT

Hello,

If you mean you want to select all values from Transaction Type except for those that are selected (excluded values) you can do as follows

Sum({< LyneType -= {'V'}, TransactionType = E(TransactionType) >} SalesValue)

If not, please make an example of your actual data and the result is should return.

Regards.

Help with Set Analysis Expression...

hopkinsc — Tue, 14 Sep 2010 15:15:05 GMT

Hi, thanks for your reply.

I have attached a sample.

If you select 'Void' from 'Transaction Type' and '1080648' from 'Transaction Id' on the left hand side as an example You should see the following..

As you can see, the Sales Value fromn 'Transaction Header' is £2.99 (This is the total of the VOID), I want this to display the total Transaction Value instead (which is a total of the transaction details £16.50, minus voids £2.99 = £13.51 ) but i do not want the VOID selection (from Transaction Type) to be effected.

Any more help would be appreciated.

Help with Set Analysis Expression...

Miguel_Angel_Baeyens — Tue, 14 Sep 2010 15:42:05 GMT

Then, once VOID is selected,

sum({< [Transaction Type] = E([Transaction Type]) >} SalesValue)

must do. But I'm guessing that the chart should display some values regardless the selections done so I think you need to control whether the user has done any selection. Or draw two different tables.

Regards.