Set Analysis - straight table and show dimension

[#1]

Hello all,

i have been trying a whole day (yesterday) to fix a problem in a straight table with the following expression:

first column expression:  #Sales: 01-01-2016 - #Sales: 18-04-2016

sum({$<Date={">=$(vStartDate) <=$(vEndDate)"}>}SalesPrice*TransactionQuantity)

Second column  expression:  Sales LY: 31-12-2014  Sales LY: 18-04-2015

Sum({1<DateNo={">=$(vStartDateLY) <= $(vEndDateLY)"}>} SalesPrice * TransactionQuantity)

In the straight table (see image bellow) you see that ( by chosen brand Diamond Point) in the straight table i still see all other brand. I only want to see the row with brand Diamond Point. for this example,  the straight table have only to show  one row from the first and second columns.

Thank you all !

[kkkumar82 · Accepted solution]

Remove 1 from your second expression

[sunny_talwar]

I think give this a try:

Sum({<DateNo={">=$(vStartDateLY) <= $(vEndDateLY)"}, Year>} SalesPrice * TransactionQuantity)

Remove 1 as suggested by Kumar and add Year so that you can ignore selection in Year field.

[#4]

Hello Sunny and kkkumar,

the following result are:

The next challenge is to show some data from the 2th column. I still don't get it why i can't see result. Diamond Point is our main core brand and i'm pretty sure that we sold that brand between 31-12-2014 and 18-04-2015

here's the current expresion i use for the second column:

Sum({$<DateNo={">=$(vStartDateLY) <= $(vEndDateLY)"}>} SalesPrice * TransactionQuantity)

The variable of the 2th expression are:

vStartDateLY                     ='$(vStartDate)' - 366

vEndDateLY                       ='$(vEndDate)' - 366

what did i wrong?

[#5]

this is the result after removing the 1

[sunny_talwar]

Did you add Year as I suggested?

Sum({<DateNo={">=$(vStartDateLY) <= $(vEndDateLY)"}, Year>} SalesPrice * TransactionQuantity)

[#7]

Hey Sunny,

yes i already did but still it doesnt't any result.

[sunny_talwar]

Would you be able to share a sample?

[#9]

Hi Sunny,

see the attachnement for my application. the problem is in the sheet "MPCS Detail".

would you be so kind to answer my question today? and would you send me your version of application or?

Thanks,

Rega

[sunny_talwar]

Try this:

Sum({<DateNo={">=$(vStartDateLY) <= $(vEndDateLY)"}, Year, Date>} SalesPrice * TransactionQuantity)