topic Previous Transaction Date Data in QlikView

Previous Transaction Date Data

Anonymous — Wed, 09 May 2012 13:54:23 GMT

Dear All,

I want to create a bar chart which would show a variance of target vs last day, in it i am facing a problem to get balance of previous transactional date.

The scenario is if i am standing on 3 january 2012, showing me some balance and 2 and 1 januray were holidays so they did not have any balance, so i should be having the balance of 31 december 2011 as it has the balance data, but i dont know the experssion for how to get the balance of 31 december 2011. The expression should get the previous date data and previous date could be any previous date having balance.

I need urgent help on this matter.

Thanks

Previous Transaction Date Data

spsrk_84 — Wed, 09 May 2012 14:19:04 GMT

Hi ,

Can u attach any sample data file.

Shivaram

Re: Previous Transaction Date Data

Anonymous — Thu, 10 May 2012 05:50:50 GMT

attached please find a sample file I have created for previous date selection.

You could see 2 text objects, 1 at center is for calculating max dates and the second below is calculating the previous date. so if I choose 3 Jan 2011, than this text object would show me the previous date which is 31 DEC 2010. if I choose 1 Feb 2011, the text object would show me 3 Jan 2011.

Urgent help would be appreciated.

Thanks

Re: Previous Transaction Date Data

IAMDV — Thu, 10 May 2012 09:42:03 GMT

Hi Ahmed,

There are various methods to get this working. However, this is the most straight forward logic to get this working. Here is what you need :

=MaxString({<WODate = {"<$(=Date(MaxString(WODate), 'MM/DD/YYYY'))"}>} Sales)

Explanation :

Step 1 : Date(MaxString(WODate), 'MM/DD/YYYY')

This expression calculates Max Date from WoDate field and formats it by using Date() function.

Step 2 : MaxString({<WODate = {"<$(=Date(MaxString(WODate), 'MM/DD/YYYY'))"}>} Sales)

This expression calculates Max Date less than the Date from Step 1. Which means previous Date in your example. And then we are extracting the Max sales value as final output.

I hope this makes sense!

Good luck!

Cheers - DV

http://QlikShare.Com

Re: Previous Transaction Date Data

IAMDV — Thu, 10 May 2012 10:18:38 GMT

Hi Ahmed,

I thought I'll simplify this expression and I have used FirstSortedValue() to simply. This is much simplier and reliable approach compared to first method. Here is the expression and I'm also attaching the QV document for your reference.

FirstSortedValue({<WODate = {"<$(=Date(MaxString(WODate), 'MM/DD/YYYY'))"}>} Sales, -WODate)

If you need more explanation about FirstSortedValue() then please look at my blog post for a video tutorial. Here is the link :

FirstSortedValue()

I hope this helps!

Cheers - DV

www.QlikShare.com

Re: Previous Transaction Date Data

Anonymous — Thu, 10 May 2012 10:51:10 GMT

Thanks a lot D V your expression of maxstring works like a charm and also firstsortedvalue was another option for it.

Now I am having another issue now to find the monthly Transactional period end Balance;

I need to show balance on month Transactional period end, so how to showit, because I tried to show monthly balance with every month’s end datebalance. Now the scenario is I want to show a line chart, which would berunning on month dimension and it shows the balance of each months end date.Like

Month: MAX(Date)of that month: Balance

Jan 31 2000

Feb 28 1500

Mar 30 1400

April 21 1800

Your help me in this issue would be highly appreciated.

Re: Previous Transaction Date Data

IAMDV — Thu, 10 May 2012 11:43:59 GMT

Hi Ahmed,

You are welcome. I would recommend you to use the FirstSortedValue solution instead of the first workaround.

I'll look into above request and I'll get back to you sometime today.

Cheers - DV

Re: Previous Transaction Date Data

IAMDV — Thu, 10 May 2012 16:31:08 GMT

Ahmed - Is it okay if I get back to you tomorrow? I have few things to finish tonight. Sorry!

Cheers ,

DV (Deepak Vadithala)

www.QlikShare.com

Re: Previous Transaction Date Data

IAMDV — Fri, 11 May 2012 08:45:35 GMT

Ahmed - Good morning. Not sure if I have understood your previous question. Do you want to show monthly closing balance for each month? I mean your dimension would be Month and your expression would be MaxString(Sales). If you want something else then please can you explain in detail? Probably with some example like your original question.

Thanks - DV

Re: Previous Transaction Date Data

Anonymous — Fri, 11 May 2012 10:32:04 GMT

ok, i think you didnt get what i mean. What i mean is if i have a data like this;

Date1, Sales

12/31/2010 12:00:00 AM, 43

01/03/2011 12:00:00 AM, 101

01/28/2011 12:00:00 AM, 98

02/01/2011 12:00:00 AM, 102

02/26/2011 12:00:00 AM, 76

03/01/2011 12:00:00 AM, 103

04/01/2011 12:00:00 AM, 104

05/01/2011 12:00:00 AM, 105

06/01/2011 12:00:00 AM, 126

01/21/2011 12:00:00 AM, 111

02/23/2011 12:00:00 AM, 112

03/22/2011 12:00:00 AM, 113

04/25/2011 12:00:00 AM, 114

05/26/2011 12:00:00 AM, 115

06/27/2011 12:00:00 AM, 116

and i need to know the monthly period end balance of this data. monthly period end means, the last working date balance of every month.

which is, if i set month in deminsion than

jan 98

feb 76

mar 113

apr 114

may 115

jun 116

attached please find a sample qvw on this data. Thanks