sum amount based on entry code

[#1]

Hello,

I am new with Qlikview and very much interested in learning it. In view of this and in hope someone to give me a headstart to develop my 1st Qlikvie application.

My source file contains records for the below fields

Business ID	Worksheet ID	Date of Booking	Entry Code	Amount

I would like to develop a report to look like this by utilizing the (Entry Code and Date of Booking)

EntryCode Group	YTD TY	YTD LY	Last Year
Premium
Acquisition
Net Premium

The Entry Codes that make-up the EntryCode Group is as below i.e.

Premium = where Entry Code in [4001, 4003, 4005] then Amount

Acquisition = where EntryCode in [6003, 6704, 6707, 6708, 6801] then Amount

Net Premium = Premium + Acquisition

Obliged if you can help me.

George

[swuehl]

Hi George,

I am pretty sure someone here can help you.

Personally, I don't understand the then statements of you group classification. Could you explain that a bit more?

Talking about Net Premium: Should this be the superset / union of the two Groups above?

If I hear about Net, I always think about a difference

Regards,

Stefan

[#3]

Hi Stefan,

Thanks for getting back to me.

The sum of Premium (say 100) plus the sum of Acquition (say -20) will make Net Premium amount.

I hope with this example I have clarified the concern.

Thanks,

George

[swuehl]

Hi George,

that looks like a point in time expression using set analysis:

You may try something like

Last Year :

sum({1<[Entry Code]={4001}+{4003}+{4005}, [Date of Booking]={"<$(=yearstart(today())>=$(=yearstart(today(), -1))"}>} Amount)

YTDTY:

sum({1<[Entry Code]={4001}+{4003}+{4005}, [Date of Booking]={"<=$(=today())>=$(=yearstart(today()))"}>} Amount)

YTDLY:

sum({1<[Entry Code]={4001}+{4003}+{4005}, [Date of Booking]={"<=$(=addmonths(today(),-12))>=$(=yearstart(today(),-1))"}>} Amount)

(For Premium, replace Entry codes for Acquisition, use sum of expressions for net)

I probably forgot a bracket or two and also maybe mistyped some expressions (hopefully not), so please take care of that (The syntax checker in expression dialog has difficulties if you use quotation marks, AFAIK).

If you want to learn more about set analysis, please have a look at the manual or

http://iqlik.wordpress.com/2010/09/11/the-magic-of-set-analysis-syntax-and-examples/

Regards,

Stefan

[swuehl]

One more thing:

the first "1" at the beginning of the set expressions has the effect, that your currect selection will not be taken into account.

I think you might want to select Business ID and Worksheet ID, so it is better to delete the "1" at the beginning (equivalent to use a "$" instead).

Have fun,

Stefan