topic Re: Choose latest data captured in QlikView

Choose latest data captured

Mon, 06 Aug 2012 09:17:01 GMT

I have an excel spreadsheet that is used to report on some logistics data per region.

Not every region has new data every day, so I was wondering if there's a way to report on the last captured day for a region, for example

Region 1 - last captured date / data is 1 Aug 2012

Region 2 - last captured date / data is 2 Aug 2012

Region 3 - last captured date / data is 2 Aug 2012

Region 4 - last captured date / data is 2 Aug 2012

Region 5 - last captured date / data is 5 Aug 2012

When I select the month, the last captured "date" per region should display (and ultimately I'd need to display the date as well) instead of having it filter if I select a day (list box) or show all for a month/year (also listbox). If I select a month/year, I'd like to see the last day each was captured and not just a summary of those captured on the last day (which is 5 Aug, and would only display Region 5, I'd like the others to display too)

Re: Choose latest data captured

Mon, 06 Aug 2012 10:56:57 GMT

you can do it many ways

post a sample file, i'll show you

Re: Choose latest data captured

Mon, 06 Aug 2012 11:35:38 GMT

I've simplified the dataset somewhat, but it should contain enough for this to work

To see what I mean, choose 2012/13 Financial Period, August, and then toggle between Day 1 / Day 2. I'll only be "choosing" the current month/financial period we're in, and the data should display the last date (with the expression that calculates stuff) it was captured in.

While toggling between Day 1 and Day 2, this will show you a bunch of regions that has data for Day 2 of August, excluding 1 that only had data for Day 1.

I'd like to show the one that had data for Day 1, with the others that had data for Day 2 (ie, the last date the bunch of records was last captured) for the Month of Aug (or whatever month is chosen, like I said, I'll always choose the latest financial period/month, but the selection should only do sum/expression work for the last date in that month that was captured)

Example:

Region, Actual Hrs, Date Last Captured

EC, 5, 1-Aug-2012

WC, 12, 3-Aug-2012

GP, 10, 1-Aug-2012

(nb, above totals are summed/expressioned for a bunch of records captured for that day, so GP will have 20 records captured for 1-Aug-2012, WC might have 12, EC might have 30)

Hope I make sense

Re: Choose latest data captured

Mon, 06 Aug 2012 11:49:25 GMT

change second expression like:

=Max({1}DATE)

Re: Choose latest data captured

Mon, 06 Aug 2012 12:01:00 GMT

That sort of works, but it "sums" the data for the other records as well, it should display the last date (the max date we have now)

In the example, selecting day 2 for Aug 2012/13:

EC, - , 1-Aug-2012

GP,285, 2-Aug-2012

KZN,306, 2-Aug-2012

MIT,118, 2-Aug-2012

WC,223, 2-Aug-2012

Deselecting day 2 so that it's just for Aug 2012/13:

EC, 163, 1-Aug-2012

GP, 603, 2-Aug-2012

KZN, 653, 2-Aug-2012

MIT, 214, 2-Aug-2012

WC, 463, 2-Aug-2012

Where it's supposed to show:

EC, 163 , 1-Aug-2012

GP, 285, 2-Aug-2012

KZN, 306, 2-Aug-2012

MIT, 118, 2-Aug-2012

WC, 223, 2-Aug-2012

Because 2-Aug-2012 is the last captured date for those regions and 1-Aug-2012 is the last captured date for EC