topic Re: Help with displaying current details in QlikView

Help with displaying current details

hopkinsc — Thu, 07 Jul 2011 08:59:09 GMT

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?

Re: Help with displaying current details

Thu, 07 Jul 2011 09:16:00 GMT

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

Re: Help with displaying current details

Miguel_Angel_Baeyens — Thu, 07 Jul 2011 09:27:10 GMT

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

Re: Help with displaying current details

Thu, 07 Jul 2011 09:40:51 GMT

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

Re: Help with displaying current details

hopkinsc — Thu, 07 Jul 2011 09:41:36 GMT

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

Re: Help with displaying current details

hopkinsc — Thu, 07 Jul 2011 09:42:49 GMT

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

thanks

Re: Help with displaying current details

Miguel_Angel_Baeyens — Thu, 07 Jul 2011 09:59:17 GMT

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

Re: Help with displaying current details

hopkinsc — Thu, 07 Jul 2011 10:02:59 GMT

Thanks Miguel, much appreciated.

Re: Help with displaying current details

hopkinsc — Thu, 07 Jul 2011 14:28:19 GMT

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

Re: Help with displaying current details

Miguel_Angel_Baeyens — Thu, 07 Jul 2011 23:04:14 GMT

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

Re: Help with displaying current details

hopkinsc — Fri, 08 Jul 2011 08:11:52 GMT

Hi Miguel, thanks for that. When i copy it into an expression, the Date part of it "Sum({< Date = {'>=$(=AddMonths"

is underlined red. Should this be my date field (StoreDate). And i don't know if im being stupid but what do you mean by...

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

Sorry, i'm not the best on set analysis and expressions!

Re: Help with displaying current details

Miguel_Angel_Baeyens — Fri, 08 Jul 2011 08:35:17 GMT

Hi,

What I mean is that in the original expression, you were using "SalesQuantity" at the end of the expression. That shows only the value of that field for the aggregation (last sold date, last store date).

So instead of coding only the name of the field, code the Sum() of the SalesQuantity values for the last 30 days, which is what I proposed above:

Sum({< StoreDate = {'>=$(=AddMonths(Date, -1))'} >} SalesQuantity)

By the way yes, it should be StoreDate or the date field you want to get the last 30 days of sales.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with displaying current details

hopkinsc — Fri, 08 Jul 2011 08:39:49 GMT

Thanks for all your help Miguel, It's very much appreciated

Re: Help with displaying current details

hopkinsc — Fri, 08 Jul 2011 09:04:11 GMT

Sorry again, I am struggling with my chart. All you have thelped me with so far worked great as i was only testing it for ONE product. I have just tried viewing ALL products for a specific store, but it only show's me ONE (The latest StoreDate and latest LastSoldDate). I need it to show every product, but where there are multiple entries for the same product that's where i would want the expression to kick in and only show the latest product.

The top 3 above should be hidden, as the 4th in the list is the latest one.

Any ideas?

Re: Help with displaying current details

Miguel_Angel_Baeyens — Fri, 08 Jul 2011 10:25:38 GMT

Hi,

You will need to add those fields to the Aggr() function. In this case I was aggregating by StoreNumber as you can see in the code above, so try replacing with the following instead

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

Change "ProductNumber" by your actual product code number field name.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with displaying current details

hopkinsc — Fri, 08 Jul 2011 10:42:40 GMT

Hi Miguel, Yes i tried this just after i asked for your help again and it worked great. I changed all expressions to include the extra dimensions and it gave me the result i was looking for.

Thanks again for all your help

The people using Qlik Community are so helpful

Re: Help with displaying current details

hopkinsc — Fri, 08 Jul 2011 10:53:53 GMT

Miguel, this i sthe last time i bother you i promise

Just wondering how the expression works which you gave me for the last 4 weeks average sales

If(FirstSortedValue(If(Aggr(Max(LastSoldDate),[Product Code],StoreNumber) = LastSoldDate, LastSoldDate), -StoreDate) = StoreDate, sum({< StoreDate = {'>=$(=AddMonths(StoreDate, -1))'} >}SalesQuantity))

I can see that this expression adds in the last months worth of sales. How would i get just the last 28 days instead of month? and i would like it to be the last 4 FULL weeks, i.e. Mon-Sun. So if i used the app today (8/7/11), it would give me the average sales between 6th June - 3rd June.

Is this too awkward?

Re: Help with displaying current details

Miguel_Angel_Baeyens — Fri, 08 Jul 2011 10:56:49 GMT

Dates are numeric values in QlikView, so you can substract to the date field 28 units (days). So something like the following should work:

Sum({< StoreDate = {'>=$(=Date(StoreDate -28))'} >} SalesQuantity)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Help with displaying current details

hopkinsc — Fri, 08 Jul 2011 12:23:55 GMT

Thanks very much i will leave you alone now