Help with displaying current details

[hopkinsc]

Hi, i am creating an application to show current stock levels for all products in all of our retail stores.

I am trying to show this information in a straight table. I have a couple of date fields which i need to use

StoreDate (which is the date the record was created)

LastSoldDate (which is the date the product was last sold)

Data comes in every night from each store and is processed early hours each morning, which means that in the space of a week, there will be 7 records per product, all with a different StoreDate and most with a different LastSoldDate.

When i try and create this chart, several lines appear per product (as it obviously shows any record processed for that product/store. I only want the chart to show the most CURRENT record.

In the example below, there are 4 lines for the same product. The 4 records have 2 different StoreDates and each different StoreDate has 2 different LastSoldDates.

I only want the chart to display the latest StoreDate with the latest LastSoldDate (The bottom line in this example)

But i also need it to do something else. I need it to completely ignore any date selection (i have attached a QV doc, the dates selections are on the top row) but i want it to accept any other selection (from the left hand side.. customer, product etc)

Can anyone help me with this please?

[#2]

Hi,

Still not sure what you want to achieve but I added a chart which does show  line with latest StoreDate independ of any selection on StoreDate. See file.

Regards,

Jonathan

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

I'd use the following expressions to get the latest date

FirstSortedValue(If(Aggr(Max(LastSoldDate), StoreNumber) = LastSoldDate, LastSoldDate), -StoreDate)

And the quantity sold this date

If(FirstSortedValue(If(Aggr(Max(LastSoldDate), StoreNumber) = LastSoldDate, LastSoldDate), -StoreDate) = StoreDate, SalesQuantity)

It seems to cumbersome, and probably will perform poor enough as to make some flag fields in the script where you get the last dates.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Hi,

Treid it again but then via a 'detour' as I struggled with date field. Define 3 variables vYear, VMonth, vDay and use those in the set analyses. I think this what you were looking for in the beginning. Check it out and let me know.

Cheers,

Jonathan

[hopkinsc]

Miguel, Thanks, that seems to do exactly what i want. But How would i put this into the script? and would it cause an un-optmised load?

Thanks

[hopkinsc]

Thanks Jonathan, Miguel posted a solution which seems to do the trick but i really appreciate your help in this

thanks

[Miguel_Angel_Baeyens]

Hi,

As a general rule, any change you do in the script when loading from QVD files will make the load unoptimized, so you might need two loads, and do the flag fields in the load from database and store into the qvd with all aggregations done.

You can easily get the last StoreDate and LastSoldDate using the Max() function in either the SQL statement or the LOAD part, and store them into variables.

Then, compare each record in the load with those variables using a conditional and use the amount to be displayed in the chart.

Thinking aloud (untested)

MaxDates:
LOAD MaxStoreDate,
     MaxLastSoldDate;
SQL SELECT Max(StoreDate) as MaxStoreDate, Max(LastSoldDate) as MaxLastSoldDate FROM Database.Table;
LET vMaxStore = FieldValue('MaxStoreDate', 1);
LET vMaxSold = FieldValue('MaxLastSoldDate', 1);
Data:
LOAD *,
     If(StoreDate = $(vMaxStore) AND LastSoldDate = $(vMaxSold), SalesQuantity) AS LastSalesQuantity;
SQL SELECT ... FROM ...;

Just an idea, it needs to fit your data model. If your document performs fine, you can leave as it is, it all depends on the volume you are loading.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[hopkinsc]

Thanks Miguel, much appreciated.

[hopkinsc]

Hi Miguel,

Sorry to bother you again..

You gave me the following expression

If(FirstSortedValue(If(Aggr(Max(LastSoldDate), StoreNumber) = LastSoldDate, LastSoldDate), -StoreDate) = StoreDate, SalesQuantity)

Could you tell me how i would change this expression to get the average sales quantity over the last 4 weeks?

Thanks

[Miguel_Angel_Baeyens]

Hi,

What about the following expression?

If(FirstSortedValue(If(Aggr(Max(LastSoldDate), StoreNumber) = LastSoldDate, LastSoldDate), -StoreDate) = StoreDate, Sum({< Date = {'>=$(=AddMonths(Date, -1))'} >} SalesQuantity))

Instead of using the field to be shown, use an aggregation function.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica