Daily amount comparison

[hkapadia]

Hello,

I'm new to QlikView. I'm trying to compare the amount of the selected date with the previous day over a dimension. I don't want to use the aggregation function as I want the amount as of the selected date & not the sum of the amounts. Please help.

[its_anandrjs]

If you have any sample data then provide which will so easy for help.

[MK_QSL]

Not clear from your question itself. Kindly provide sample data along with your requirements clearly.

Thanks..

[hkapadia]

This is long but hopefully it helps to understand the issue.

Here is my sample data.

Code	Date	Amount
1	2014-07-15	8967514.84
1	2014-07-16	8960822.07
1	2014-07-17	9083355.07
1	2014-07-18	9412298.67
1	2014-07-21	9219840.22
2	2014-07-15	59600.12
2	2014-07-16	63698.52
2	2014-07-17	64859.21
2	2014-07-18	64031.44
2	2014-07-21	60052.22
3	2014-07-15	191902.75
3	2014-07-16	190116.72
3	2014-07-17	192167.07
3	2014-07-18	177934.60
3	2014-07-21	173803.55
4	2014-07-15	42569672.83
4	2014-07-16	42569672.83
4	2014-07-17	42569672.83
4	2014-07-18	46569672.83
4	2014-07-21	46569672.83
6	2014-07-15	227998734.67
6	2014-07-16	229967873.28
6	2014-07-17	232085796.94
6	2014-07-18	228306880.74
6	2014-07-21	229696311.00

User will select a field from the date list box & the output is a table with 4 columns:

Column 1: Code

Column 2: User selected Date (Date1)

Column 3: Date1 - 1 (I want the previous business day & not the immediate previous day)

Column 4: Difference between Column 2 & Column 3

When user selects 7/21/14 as the date, here is what I'm getting as the output which is not what I want.

Codes	2014-07-21	7/20/2014	2014-07-21 vs 7/20/2014
	-	$39,054,478,451.70	-
1	-	$1,464,116,932.47	-
2	-	$39,772,418.88	-
3	-	$44,031,492.39	-
4	-	$4,040,758,402.75	-
6	-	$33,465,798,362.17	-

There are 2 issues here.

1) I don't want to sum over the amount for the dates.

2) When user selects the date of 7/21/14, the 2nd date should be 7/18/14 (Friday - previous business day) & not 7/20/14 which is a Sunday.

Thanks for your help.

[#5 · Accepted solution]

Hi Hitesh

If I have understood you correctly I’m guessing you are looking for an output something like this:

Code	Selected Date	Previous Working Day	Amount On Selected Day	Amount On Previous Working Day	Difference
1	2014-07-21	2014-07-18	$9219840.22	$9412298.67	-$192458.45
2	2014-07-21	2014-07-18	$60052.22	$64031.44	-$3979.22
3	2014-07-21	2014-07-18	$173803.55	$177934.60	-$4131.05
4	2014-07-21	2014-07-18	$46569672.83	$46569672.83	$0.00
6	2014-07-21	2014-07-18	$229696311.00	$228306880.74	$1389430.26

I believe you wish to be able to calculate the date of the previous working day and then use the value to measure the difference between the amounts on the selected day and the previous working day.  Assuming my interpretation is correct you can use the method below, (one of many I’m sure). I renamed your ‘Date’ field to ‘ActDate’ to distinguish it from the ‘Date’ format keyword.

Firstly open up QlikView's Script Editor and paste in your data:

LOAD * INLINE [
Code, ActDate,   Amount
1,    2014-07-15,     8967514.84
1,    2014-07-16,     8960822.07
1,    2014-07-17,     9083355.07
1,    2014-07-18,     9412298.67
1,    2014-07-21,     9219840.22
2,    2014-07-15,     59600.12
2,    2014-07-16,     63698.52
2,    2014-07-17,     64859.21
2,    2014-07-18,     64031.44
2,    2014-07-21,     60052.22
3,    2014-07-15,     191902.75
3,    2014-07-16,     190116.72
3,    2014-07-17,     192167.07
3,    2014-07-18,     177934.60
3,    2014-07-21,     173803.55
4,    2014-07-15,     42569672.83
4,    2014-07-16,     42569672.83
4,    2014-07-17,     42569672.83
4,    2014-07-18,     46569672.83
4,    2014-07-21,     46569672.83
6,    2014-07-15,     227998734.67
6,    2014-07-16,     229967873.28
6,    2014-07-17,     232085796.94
6,    2014-07-18,     228306880.74
6,    2014-07-21,     229696311.00
];

Save the application and load the data.  Then open the Variable Overview (Located on the Settings menu) and create a new variable called vLastWorkingDate.

In the 'Definition' box type in this formula which calculates the previous working day:

=Date(firstworkdate(ActDate-1,1),'YYYY-MM-DD')

Next, create a straight table and add two dimensions to it.  The first dimension is your 'Code' field.  The second is a calculated dimension that will pull through your selected date.  The formula for it is:

                              =GetFieldSelections(ActDate)

Label the dimensions respectively as 'Code' and 'Selected Date'.

Now you have the dimensions in place you need to add the expressions.  There are four expressions that you need to add:

Expression 1:

            =vLastWorkingDate  

                                        (Labelled 'Previous Working Day')

Expression 2:

            =Sum(Amount)   
                                        (Labelled 'Amount On Selected Date')

Expression 3:

            =Sum({$<ActDate={">=$(vLastWorkingDate)<=$(vLastWorkingDate)"}>} Amount)    

                                        (Labelled as 'Amount On Previous Working Day')

Expression 4:

            =Sum(Amount)

               -

              Sum({$<ActDate={">=$(vLastWorkingDate)<=$(vLastWorkingDate)"}>} Amount)    

                                        (Labelled as 'Difference')

Add a list box to display your ActDate field values and format your straight table chart to your liking. I would recommend you enable the 'Always One selected Value' option on the General tab of the list box's properties.  If it is greyed out then select one date in the listbox before opening its properties.

You should now have a table where when you select a date then for each code you will see the values of amount on selected day, amount on the previous working day and the difference between the two, as displayed in the table at the top of this post.

Hope that helps

Kind regards

Steve

[hkapadia]

That worked exactly how I wanted it.

Thanks so much Steven for your help.

[#7]

Hi Hitesh

You're welcome, I'm pleased it has helped you.  Thanks for marking my answer as correct.

Kind regards

Steve