topic Re: Set Analysis - straight table and show dimension in QlikView

Set Analysis - straight table and show dimension

Wed, 25 Nov 2020 16:16:04 GMT

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 !

Re: Set Analysis - straight table and show dimension

kkkumar82 — Tue, 19 Apr 2016 07:55:37 GMT

Remove 1 from your second expression

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 08:01:20 GMT

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.

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 08:08:35 GMT

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?

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 08:09:59 GMT

this is the result after removing the 1

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 08:12:02 GMT

Did you add Year as I suggested?

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

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 08:17:10 GMT

Hey Sunny,

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

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 08:27:43 GMT

Would you be able to share a sample?

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 08:33:42 GMT

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

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 08:42:32 GMT

Try this:

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

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 08:55:25 GMT

Hey Sunny,

You're incredible . My app is now working.

thank you so much.

Have a nice day !

Greets,

Rega

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 08:56:18 GMT

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

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 09:28:47 GMT

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 !

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 09:36:18 GMT

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.

Re: Set Analysis - straight table and show dimension

Tue, 19 Apr 2016 09:47:09 GMT

Hey Sunny,

what's your advice?

using the {<Year, otherDateFieldsMostLikely>} ?

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 09:50:37 GMT

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).

Re: Set Analysis - straight table and show dimension

sunny_talwar — Tue, 19 Apr 2016 10:33:13 GMT

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