Find only 1 Row per order - Is it possible with set analysis ?

rob_greene — Tue, 16 Jul 2013 15:30:15 GMT

Hello All, I hope someone can throw some light on this for me. Even if it's to tell me it can't be done. 😉 (Always a challenge)

Find and show HIGHEST value in a dimension within a date range.

I would like to restrict the amount of rows shown on a straight table (Table 2) using set analysis to 1 row per order for a given number of scenario's

Variables. (not sure if all these are necessary, there may be an easier way of doing this)

//If there is no date selected use the max date in the Date Island

vReportingdate =if(not ISnull(only([Date Island])),only([Date Island]),vMaxDate)

vMaxDate =10/04/2013

vLastUpdated =if(only(%NoOfDaysSinceUpdate)=0,'$(vReportingDate)','<=$(vReportingDate)>$(vReportingDateFrom)')

vLastUpdateLimit =%NoOfDaysSinceUpdate

vReportingDateFrom =Date((vReportingDate - vLastUpdateLimit),'DD/MM/YYYY')

vAmountLimit =%Amount_Limit

//////////////////////////////////////////////////////////////////////

Scenario 1

Default %NoOfDaysSinceUpdate = 0

No Date Island value selected.

No Bookmark

When no date is selected in Date Island I want table '2. Set Expression' to show any orders that have been updated on that date.

If there is and %AmountLimit selected then also select orders below that limit.

The expression used is this and does what it needs to in the scenario.

sum({<
[Update Date] = {'$(vLastUpdated)'},
Amount = {'<=$(vAmountLimit)'}
>}Amount)

//////////////////////////////////////////////////////////////////////

Scenario 2

%NoOfDaysSinceUpdate = 30

No Date Island value selected.

BookMark 10/04/2013 - 30 Days

Show the latest updated version to any orders that have been updated between the max date and the (max date - %NoOfDaysSinceUpdate)

Order1 Version 6 Correct

Order 3 Version 6 Correct

Order 2 Version 3,4,5,6 INCORRECT, I get 4 rows because 4 versions have been updated between <=10/04/2013>11/03/2013

//////////////////////////////////////////////////////////////////////

Scenario 3

%NoOfDaysSinceUpdate = 60

20/03/2013 selected in Date Island

BookMark 20/03/2013 - 60 Days

Show the latest updated version to any orders that have been updated between the max date and the (max date - %NoOfDaysSinceUpdate)

Order1 Version 5 Correct

Order 3 Version 5 Correct

Order 2 Version 1,2,3,4,5 INCORRECT, I get 5 rows because 5 versions have been updated between <=20/03/2013>19/01/2013

//////////////////////////////////////////////////////////////////////

These are just examples to prove the functionality works or not.

The app is attached.

I would be extremely grateful for any help or pointers in the right direction.

Many thanks

Rob

Re: Find only 1 Row per order - Is it possible with set analysis ?

Gysbert_Wassenaar — Tue, 16 Jul 2013 16:49:32 GMT

You need an if statement because the set is calculated once for the entire chart, not per row. See attached qvw.

Re: Find only 1 Row per order - Is it possible with set analysis ?

rob_greene — Tue, 16 Jul 2013 19:56:47 GMT

You are a star Gysbert, thank you very much.

If(Version=max({<

[Update Date] = {'$(vLastUpdated)'},

Amount = {'<=$(vAmountLimit)'}

>}total <Order> Version),

Sum({<

[Update Date] = {'$(vLastUpdated)'},

Amount = {'<=$(vAmountLimit)'}

>}Amount)

)

I would never have thought if that, but now I can see many uses in similar circumstances.

Best Regards

Rob

topic Find only 1 Row per order - Is it possible with set analysis ? in QlikView

Find only 1 Row per order - Is it possible with set analysis ?

Re: Find only 1 Row per order - Is it possible with set analysis ?

Re: Find only 1 Row per order - Is it possible with set analysis ?