Set Analysis query

[Dayna]

Hello,

I am trying to achieve what I remembered as quite an easy thing, but seem to be struggling.

I have several data islands set up for a comparion using Set Analysis, my formula is:

sum

({<[Year Week]={$(=concat(Distinct YearWeek1,','))},Area={$(=Area1)}>}[Supplied Quantity

)

But I get either - or 0 back as my result... Can someone help me please??

Kind Regards,
Dayna

[#2]

We're going to need some more information. What do the values in your fields look like? What are YearWeek1 and Area1? Variables?

One of the best tips when using Set Analysis and a dollar sign expansion is to set up a straight table chart with your expression, but don't give the expression a label. Then, when the chart is rendered, the label will be your Set Analysis expression with the dollar sign expansions evaluated. You may have to extend the column width and height to see the whole expression.

I'd guess that your dollar sign expansions aren't evaluating to what you need them to. Using the tip above should tell you if that is the problem or not.

The expression does give you values when used without the Set Analysis, correct?

EDIT: I just looked a little closer. Is Year Week a string? Is Area? If so, you need quotes around the dollar sign expansions.

Area={'$(=Area1)'}>}...

If Year Week is a string, then it gets a little tricky, because of the concat. You would need something like this:

...[Year Week]={'$(=concat(Distinct YearWeek1,Chr(34) & ',' & Chr(34)))'}...

Adding quotes around the dollar sign expansion would give you quotes around the entire list, but (if it's a string) you need quotes around each value. The Chr(34) & ',' & Chr(34) gives you that.

[Dayna]

Hello NMiller,

Many thanks for your reply, the trick for removing the label is really helpful, thank you!!

I have tried the expression in a standard table and I get the results back, and playing with it this morning, if I remove the bit about area I get resulsts for the YearWeek, but not if I add anything else.

YearWeek is a small expression from the loading script to produce Year&'.'&Week from the date information I have. Area however is a series of area's around the UK, i.e. North West, South West, London, etc. I have Area1, YearWeek1 etc as these are Data Islands that I've produced in my data set. I will try and add quotes to the area and see how it goes, thank you for your help!

Kind Regards,
Dayna

[Dayna]

Hello,

If I select the area I now get results, but I get 0 if nothing is selected... My expression is currently:

sum({<Area={'$(=concat(Distinct Area1,','))'}>} [Supplied Quantity])

Kind Regards,

Dayna