Current and previous month

[mario-sarkis]

please i have a list of ID  and a period date and an ammount

i need to show a pivot table of the sum of ammount like  that

ID       CURRENT Month        PreVious Month       Lost Amount

how can i write the expression

[sunny_talwar]

Just looking at the Column Names won't be much helpful, Can you provide few lines of data with the expected output to get a better response?

Best,

Sunny

[mario-sarkis]

hi sunny my question  is  i have data

ID     PeriodDate       Amount

x       Jun-2014       1000

x       Jun-2014       500

x       July-2014      1000

x       aug-2014       500

My requirment is i need to show an Pivot table that contain  (sum of amount of the current and previous month in orger to find the lost amount)

Lets suppose my current month is July  and my previous is jun

ID             CurrentMonth          PreviousMonth

X              1000                        1500

and once i select to August my current month will be August and previous month will be july                        

ID        CurrentMonth        PreviousMonth

X         500                          1000

of caurse in my date have many ID

Please note that my periodDate has a num form and i created the monthName field

Thank you hope you can help.

[mario-sarkis]

hi sunny this my sample  my request is i need to show a table that calculate the amount in max date VS  the amount in the Previous date right before it here is my example:

ID	Balance	Date
07019	20994.09	3/19/2015
07019	21035.58	11/26/2014
07019	21077.07	12/31/2013
07019	20007	1/31/2014
07019	17000	2/28/2014
07019	18000	4/30/2014
07019	12000	5/31/2014
07019	10000	6/30/2014
07019	14000	7/31/2014
07019	25000	8/31/2014
07019	55000	9/25/2014
07019	153000	10/17/2014
00534	144551	3/19/2015
00534	153420	11/26/2014
00534	161708	12/31/2013
00534	25000	1/31/2014
00534	26000	2/28/2014
00534	29000	4/30/2014
00534	35000	5/31/2014
00534	45000	6/30/2014
00534	26000	7/31/2014
00534	29000	8/31/2014
00534	22000	9/25/2014
00534	21000	10/17/2014

in this Case i need my output will show if no selection on any field the Amount on max date VS the previous one in my example :

ID                       MaxDate (3/19/2015)            PreviousDate(11/26/2014)       Lost Amount

07019                20994.09                                21035.58                                    20994.09-21035.58=(41.49)

00534                144551                                   153420                                       153420-144551=8869

if i select on for example  10/17/2014 i get

ID                       MaxDate (10/17/2014)            PreviousDate(9/25/2014)       Lost Amount

07019                153000                                      55000                                        153000  - 55000=98000

00534                21000                                        22000                                         21000-22000  =(1000)

[settu_periasamy]

Hi,

you can achieve this using the below Expression.

Current

=sum({<Date={'$(=Date(Max(Date),'M/D/YYYY'))'}>}Balance)

Previous:

=sum({<Date={"$(=Date(Max({1}{<Date={'<$(=Date(Max(Date),'M/D/YYYY'))'}>}Date),'M/D/YYYY'))"}>}Balance)

Attached the sample

[settu_periasamy · Accepted solution]

Hi,

Could you please close the thread, if you got the answer?

[mario-sarkis]

Hi settu

Thank you for you reply

if my date form is an num how can i write this expression ?
Regards

[settu_periasamy]

The given expression will work if the date format as a number..

Date (your date field, 'M/D/YYYY')

You can Change the format based on your qlikview document.

[mario-sarkis]

if you can explain this expression part by part i will be very thankfull

"sum({<Date={"$(=Date(Max({1}{<Date={'<$(=Date(Max(Date),'M/D/YYYY'))'}>}Date),'M/D/YYYY'))"}>}Balance)"

Sorry for disturbing,
Thank you so much

[settu_periasamy]

No problem.

The inner max expression

will give the maximum date based on your Date Listbox Selection (by default maximum date)

=sum({<Date={"$(=Date(Max({1}{<Date={'<$(=Date(Max(Date),'M/D/YYYY'))'}>}Date),'M/D/YYYY'))"}>}Balance)

the Max{1}Date will also give the Maximum Date, This won't consider Date field Selection because or {1}

=sum({<Date={"$(=Date(Max({1}{<Date={'<$(=Date(Max(Date),'M/D/YYYY'))'}>}Date),'M/D/YYYY'))"}>}Balance)

Finally, this value Should be Less than Selected Maximum value, i.e the second maximum.

For, e.g if we select 3/19/2015, the set expression will give result like the below

=Max({<Date={'<3/19/2015'}>}Date)

Here, the Max Date of Less than 3/19/2015 is  11/26/2014.

I'm not in good in explain. :-), but this is logic.

when you remove the label name from your expression, you can check this.

Hope this helps