Previous Transaction Date Data

[Anonymous]

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

[spsrk_84]

Hi ,

Can u attach any sample data file.

Shivaram

[Anonymous]

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

[IAMDV · Accepted solution]

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

[IAMDV]

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

[Anonymous]

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.

[IAMDV]

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

[IAMDV]

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

[IAMDV]

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

[Anonymous]

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