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 !

[#2]

Hey Sunny,

You're incredible . My app is now working.

thank you so much.

Have a nice day !

Greets,

Rega

[sunny_talwar]

I am glad I was able to help. Please mark any helpful answers

[#4]

Hey Sunny,

in the attachment you see the version 1.9.3.

in the sheet MPCS Detail you see straight table called Stock per MPCS and Warehouse. the expression Current Stock QTY and Current Stock (€) have a set analysis condition 1. but if i change this condition to $, no data will shown for these two expressions.

Would you be so kind to check my application and give me advice or answer?

Thanks !

[sunny_talwar]

Your Year and StockQuantity data are not correctly joined here:

As soon as your select a particular year, the only rows left are the one with StockQuantity 0. You will either need to use {1} or {<Year, otherDateFieldsMostLikely>} to get this to work, but then the information won't filter out based on Year or any other date field.

[#6]

Hey Sunny,

what's your advice?

using the  {<Year, otherDateFieldsMostLikely>} ?

[sunny_talwar]

I would suggest fixing the data model if stock quantity is based on Date field. Meaning every year would have a different StockQuantity. If they are unrelated, then you can use the set analysis {1} or {<Year, otherDateFieldsMostLikely>} based on your requirement.

{1} -> Expression won't change based on any selection made

{<Year, otherDateFieldsMostLikely>} -> Expression will change results based on all fields except the ones which are added in your set analysis (such as Year).

[sunny_talwar]

If you got what you are looking for, please consider marking helpful answers (as requested before also). The helpful answers help people in future who are trying to look a solution for similar problems. There is no limit to how many helpful answers you can select, but use your discretion only to mark those replies as helpful which you really thought helped you out.

Qlik Community Tip: Marking Replies as Correct or Helpful