Question about section Analysis

[#1]

Hi

I have a data set with as follows:

Sales_Tmp1:

LOAD * INLINE [

Order Date, Country, # Sales

20/01/2013, USA, 110

20/02/2013, USA, 110

18/03/2013, USA, 110

20/04/2013, USA, 110

20/05/2013, USA, 110

..

]

Which I then read in to set the month

Sales:

LOAD

  date([Order Date]) as Date,

  date([Order Date],'YYYY-MM-DD') as DateYYYYMMDD,

  year(date([Order Date])) as Year,

  month(date([Order Date])) as Month,

  Country       as Country,

  [# Sales]     as [# Sales]

RESIDENT

  Sales_Tmp1;

I have two variables

set vSales1	= "sum({$<[DateYYYYMMDD] =  {'2013-01-20'}>} [# Sales])";
set vSales2	= "sum({$<[Month]        =  {'Mar'}>}        [# Sales])";

Without selecting anything the two expressions show the relvent values.

If I select the Month of April, the first expression shows nothing, and the second expression shows the sales figures for March.

I am confused as the two expressions are behaving differently, even though as far as I can see the format of the expressions is the same.

I was wondering if someone can please explain why the two expression behave differently.

The attached screen shot shows the charts I am looking at.

Thanks

Andy

[dmohanty]

Hi,

Clearly in the 1st Variable, you have used DateYYYYMMDD in the Set Analysis. So to make your Charts behave correctly, you have to select any other dimension apart from DateYYYYMMDD. (try using DateYYYYMMDD in list box, you will understand)

For 2nd variable, you have used Month in Set Analysis. You have to select any other dimension apart from Month to make the charts behave correctly.

That's why when you were selecting any other Months, its showin the Sales2 value as Month has been used in the Set Analysis. If you select any value from DateYYYYMMDD, then the Sales1 would show up for any selection.

Try and let me know.

[#3]

Hi

My understanding is that no matter what I have selected, both expressions should return a value.

If I select the month of 'April', the first expression on the date in Jan returns a value of 0, whereas the expression for the sales in March returns the sales in March.

I would expect both expressions to return a value.

Thanks

Andy

[dmohanty]

Hi,

Use this expression in the first Expression, and check if it helps:

sum({$< Month = ,  [DateYYYYMMDD] =  {'2013-01-20'}>} [# Sales])

[simondachstr]

I think the issue is, that the first expression is taking [DateYYYYMMDD] =  {'2013-01-20'} as well as [Date] into consideration. Now if you select April, [#Sales] will be 0 because [Date] field is filtering only the April values.

Try out the following expression:

set vSales1

= "sum({$<[DateYYYYMMDD] =  {'2013-01-20'}, [Date]= >} [# Sales])";

or

set vSales1

= "sum({$<[DateYYYYMMDD] =  {'2013-01-20'}, [Date]= ,Month=>} [# Sales])";

[#6]

Hi

That works thanks, but I still do not understand why my original expression didn't work.

Thanks

Andy

[dmohanty · Accepted solution]

Clearly, as I have stated before, the Dimension you are using in Set Analysis to filter, should not be used in List Box selection or else should not be used in Dimension in charts.

Similarly, add  DateYYYYMMDD in the dimension of chart and use below in 2nd expression:

sum({$<[Month]   =  {'Mar'},  [DateYYYYMMDD] = >}  [# Sales])

In case your problem resolved, mark this post as answered not to confuse others/help others!!!

[#8]

Interesting

The first expressions doesn't work, but the second one does

[dmohanty]

Cool,

Better add both the Dimension to the expressions, as I have provided in both expressions. Now whatever the selections you make in List Box, the chart will behave properly.