topic Re: How to set a Dimension value equal to another selected value in QlikView

How to set a Dimension value equal to another selected value

sspe — Wed, 29 Jan 2014 07:28:24 GMT

Hi,

I need some help on doing a sum of values so I can compare some Budget figures with my actual figures. My actual figures are in a Facttable and is related to a Posting Date dimension. By Budget numbers are in a different table which is linked to a Budget Date dimension. The Fact table and budget numbers are linked through a Campaign table, so I can get my budget and actuals by campaigns.

The problem I'm having is that when I select e.g. December 2013 as my PostingDate , then I'd like to set "select" that for my Budget Date as well. To me it sounds quite simple, but I haven't had much luck with it yet.. :-(.

I have made the following expression to give the budget numbers, but that doesn't seems to work (and I've tried different variations as well.... )-

sum({$<BudgetDate_YearMonth ={"=$(PostingDate_YearMonth)"}>} acbudget_Numbers)

I'm just trying to say that the YearMonth value I have selected as my PostingDate should also be set as the Budget Date, but I simply don't know how to do it correctly? I'm still new to QV, so I'm still not fluent in Set analysis - and also I'm not sure if Set analysis is the right way to solve the issue?

Regards

Steen

Re: How to set a Dimension value equal to another selected value

Wed, 29 Jan 2014 08:19:49 GMT

Assuming that you want the selections to mirror each other exactly and the time scales are identical, which would make sense with your current set analysis, you can use P (possible) instead, so maybe try

sum({$<BudgetDate_YearMonth =P(PostingDate_YearMonth)>} acbudget_Numbers)

Re: How to set a Dimension value equal to another selected value

Wed, 29 Jan 2014 09:05:41 GMT

Hi,

You can use the concat function. This method works well if your field values do not contain the delimiter that is ,

Ex: Budget is <BClientID> and the user selects the ClientID through a ListBox. ClientID and BClientID have the same values

Here below, the methd works for integer and string values. If you have integer, you may remove the part at the right and left of concat puting some extra double quotes.

sum ({<BClientID= {$(='"' & concat(Distinct ClientID, '","') & '"')} >} Budget)

Please adapt to your case.

Fabrice

Re: How to set a Dimension value equal to another selected value

sspe — Wed, 29 Jan 2014 20:00:45 GMT

Hi,

Thanks a lot - this seems to work. I'm not sure I fully understand why the P is needed though but I'll have to read up on that.

Regards

Steen