topic Re: Manage Dataset based on user's actions in QlikView

Manage Dataset based on user's actions

Fri, 03 Jan 2014 09:58:11 GMT

Hello to everyone.

Could someone help me with the following problem please?:

I have a dataset that has Two entities: Portfolio and Value

Value has Date property, something like this:

Portfolio

PositionId	PositionName
1	MS
2	APPL
3	SMI

Value

Date	PositionId	Money
20/01/2012	1	4
20/01/2012	2	56
21/01/2012	1	6
21/01/2012	2	60

Also I have two calendars which sets two date variables.

And I have to dynamicly calculate value based on some formula for each position. The operands of the formula are two Money from Value entity for selected date variables.

I reached this in Chart(table-view) control and I can display this calculated value there because Chart automaticly groups all data by Position.

But now I need these calculated things as an operands for another formula and I wander If there is some way to dynamicly manage dataset.

Let's say I would have

Portfolio

PositionId	PositionName	Calculated Property
1	MS	#1
2	APPL	#2
3	SMI	#3

And these Calculated property I could Update based on some actinos that user does.

Whar do you think?

Re: Manage Dataset based on user's actions

sujeetsingh — Fri, 03 Jan 2014 10:04:20 GMT

Alek ,

You explained that you are going to quote some value based on actions from the user in your dashboard.

Now you need to provide details of the action .

May what i think you are just going to raise a flag based on selection of some filters.

Please explain in more detail.

Re: Manage Dataset based on user's actions

Fri, 03 Jan 2014 10:16:18 GMT

Hello sujeetsingh,

User selects two dates in two calendars. These calendars manages two variables (vFromDate and vToDate)

The formula I need to calculate is something like Money(for vToDate) / Money(for vFromDate)

As you can see, the formula itself is working with ALL Value dataset and If I use this formula as an expression in Chart who's dimention is Position - the chart does the rest of job and it GROUPS Value entity for each Position.

But now I need to have this calculated thing in dataset I think. But because this thing depends on what vFromDate and vToDate selected - I can't define it durign ETL.

Let's say I want to know which Money(for vToDate) / Money(for vFromDate) is the BIGGEST (for what Posotion).

Re: Manage Dataset based on user's actions

Anonymous — Fri, 03 Jan 2014 18:28:21 GMT

Can't help without an example, but it looks like the expression should use set analysis where vToDate and vFromDate are used in set modifiers.

Re: Manage Dataset based on user's actions

Sat, 04 Jan 2014 13:45:53 GMT

Here is an example

https://dl.dropboxusercontent.com/u/36492054/test.qvw

There is a column "Max Possible Return" that is a calculated property based on user selected dates

I need to find the Biggest calculated value and create the following diagram:

X is the "Max Possible Return" value

Y is the Portfolio's count for x-gap. (x-gap is 10 for positive and 5 for negative).

I really have no Idea how to do this.

Re: Manage Dataset based on user's actions

Anonymous — Tue, 07 Jan 2014 16:38:35 GMT

Aleksander,

The expressions for your text boxes will be (my additions in bold).

Max Return:
= Round((
max(aggr(
Sum({$<PositionHistoryDate = {'$(vToDate)'}, OpenDate ={"<=$(vToDate)"}, CloseDate ={">=$(vFromDate)"}>}PositionHistoryValue) /
Sum({$<PositionHistoryDate = {'$(vFromDate)'},OpenDate ={"<=$(vToDate)"}, CloseDate ={">=$(vFromDate)"}>}PositionHistoryValue) - 1
,PositionId))
) * 100, 0.1) & '%'

Max Possible Return:
= Round((
max(aggr(
Max({$<PositionHistoryDate = {"<=$(vToDate)"},PositionHistoryDate = {">=$(vFromDate)"}, OpenDate ={"<=$(vToDate)"}, CloseDate ={">=$(vFromDate)"}>}PositionHistoryValue) /
Sum({$<PositionHistoryDate = {'$(vFromDate)'},OpenDate ={"<=$(vToDate)"}, CloseDate ={">=$(vFromDate)"}>}PositionHistoryValue) - 1
,PositionId))
) * 100, 0.1) & '%'

I can't help you with the bar chart because I have no idea from your data what the Portfolio count or x-gap is.

Regards,
Michael