Set Analysis with field comparison

[#1]

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)

[#2]

Hello Abraham,

did you check my lastest post to your thread

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

?

RR

[#3]

Yes it did not give me the desired results

[#4]

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!!!!

[#5]

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

[#6]

Hello Roland

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

Thank You ver much.

Kind Regards

Abraham

[#7]

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.

RR

[#8]

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).

[#9]

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.

RR

[#10]

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