topic Set Analysis with field comparison in QlikView

Set Analysis with field comparison

Thu, 25 Nov 2010 13:33:20 GMT

I want help with month comparison. I want help on a set analysis expression for selection such that when ReceiptMon is selected it would display records which salesmon is the same as ReceiptMon. I have tried this but no records .

For example if Jan is selected for ReceiptMon it should display records whose ReceiptMon is Jan and SalesMon is Jan.

Any help would be appreciated.

SUM({$<SalesMon = ReceiptMon>} ReceiptAmount)

Set Analysis with field comparison

Thu, 25 Nov 2010 14:26:45 GMT

Hello Abraham,

did you check my lastest post to your thread

http://community.qlik.com/forums/p/37312/146582.aspx#146582

Set Analysis with field comparison

Thu, 25 Nov 2010 15:02:55 GMT

Yes it did not give me the desired results

Set Analysis with field comparison

Fri, 26 Nov 2010 08:31:09 GMT

Hello Roland,

Sorry I did not explain myself well. Actually the Metric Cash Collection is a ratio which is expresed as:

Cash Collection / Total Sales.

So I would also require the total sales for the Selected period. Thus I would require the Sales Month and not the Receipt Month to be used as the dimesion for the selection. Hope to hear from you.

I appreciate and am grateful for your Ideas so far.

Thank You!!!!

Set Analysis with field comparison

Fri, 26 Nov 2010 11:15:23 GMT

Hi Abraham,

in my second Version I put the Sales(Year)Month and the Receipt(Year)Month in each case together into one field. So it is very easy to sum up ReceiptAmount and SalesAmount for the same Monthbecause t there is only one Date (Year, Month) left. This seems to me to be a approriate data modell.

To get the Cash Ratio as described above just create a new expression in the straight table with sum(ReceiptAmount) / sum(SalesAmount). If you like you can format it as a percentage (Number tab --> format settings)

HtH

Roalnd

Set Analysis with field comparison

Fri, 26 Nov 2010 12:41:21 GMT

Hello Roland

I am very Grateful. It works well. I appreciate your efforts and concern.

Thank You ver much.

Kind Regards

Abraham

Set Analysis with field comparison

Fri, 26 Nov 2010 15:58:34 GMT

Hello Abraham,

glad to help you. Don't hesitate to post again. Would you mark my aswer above as correct so that the thread will be flagged green and can be seen as closed.

Set Analysis with field comparison

Sat, 27 Nov 2010 13:02:19 GMT

Hello Roland,

Sorry to come back, after critical examination I am not still getting the desired result. From the version 2 of your application If January is seleceted as the Mon it brings receipt relating to all sales made in January irrespective of the month of the receipt. I am expecting the receipt amount to be 3000 and not 4500 ( The extra receipt amont of 1500 being for Feb and Mar receipt with sale in Jan).

Set Analysis with field comparison

Sat, 27 Nov 2010 15:04:59 GMT

Hello Abraham,

sorry, this was my fault. I didn't catch your problem exactly. Communication in someones mother language (for me german) can be hard and in a foreign language (english) even harder. Take a look at the new version.

Set Analysis with field comparison

Mon, 29 Nov 2010 08:49:13 GMT

Hello Roland,

The Sales date and the Sales number in the Receipt table shows the relationship between the sales and the receipt. The solution provides all the receipt for the selected month. Example: For the month of february I am expecting receipt for only Sales number 10003 with Sales date 15-Feb-2010, because sales number 10002 was made in January which does not give me a true Picture.

That is why I think that the problem is beyond only Data modelling. I think a Set analysis expresession for comparing the selected month of the sales date is the same as the month of the Receipt date.

With Kind Regards

Abraham

Set Analysis with field comparison

Mon, 29 Nov 2010 16:29:13 GMT

Hello Abraham,

my first idea wasn't so bad. And with it you pointed into the right direction. Look at your first post and try your expression with a good old if-statement:

sum(IF( ReceiptMon= SalesMon, ReceiptAmount, 0 ))

Hope this helps right now

Re: Set Analysis with field comparison

themandrake — Tue, 13 Jan 2015 05:34:36 GMT

hi Abraham,

this is an old question you have and i see that It has been unanswered since 2010. might be a closure to it if this works:

=sum( {$<SalesMon = {"=ReceiptMon"}>} ReceiptAmount)

best regards