topic Re: Current and previous month in QlikView

Current and previous month

mario-sarkis — Mon, 27 Jul 2015 14:54:37 GMT

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

Re: Current and previous month

sunny_talwar — Mon, 27 Jul 2015 14:58:30 GMT

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

Re: Current and previous month

mario-sarkis — Tue, 28 Jul 2015 08:19:56 GMT

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.

Re: Current and previous month

mario-sarkis — Thu, 30 Jul 2015 13:38:30 GMT

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)

Re: Current and previous month

settu_periasamy — Thu, 30 Jul 2015 15:51:05 GMT

Hi,

you can achieve this using the below Expression.

Current

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

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

Attached the sample

Re: Current and previous month

settu_periasamy — Fri, 31 Jul 2015 06:32:41 GMT

Hi,

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

Re: Current and previous month

mario-sarkis — Fri, 31 Jul 2015 10:35:46 GMT

Hi settu

Thank you for you reply

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

Re: Current and previous month

settu_periasamy — Fri, 31 Jul 2015 11:40:00 GMT

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.

Re: Current and previous month

mario-sarkis — Fri, 31 Jul 2015 12:01:32 GMT

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

Re: Current and previous month

settu_periasamy — Fri, 31 Jul 2015 12:52:40 GMT

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

Re: Current and previous month

mario-sarkis — Fri, 31 Jul 2015 12:58:16 GMT

Thank you settu, i can tell that your good in explaining

Re: Current and previous month

mario-sarkis — Mon, 03 Aug 2015 13:09:47 GMT

Hey Settu,

how can i show the loss per month in a line Chart ?

coz once i selected on a month lets say: if my selection is on oct-2014 as (Current) the (Previous) is showns as (oct-2013) else i want it to show as sep-2014.

when no selection is corrected as your expression but it shows as an error in a line chart per month.

Hope you can help

Re: Current and previous month

mario-sarkis — Thu, 06 Aug 2015 08:51:14 GMT

Hi Settu settu_periasamy

I have a question for you, have any idea how i can calculate this Loss in text for all ids?

Please help

Thank you:)

Re: Current and previous month

settu_periasamy — Thu, 06 Aug 2015 09:43:06 GMT

Hi Mario,

Do you want all the ID's and Loss Amount in Single TextBox?,

Try this in your text box.

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

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

Re: Current and previous month

mario-sarkis — Thu, 06 Aug 2015 10:41:38 GMT

this is exactly wut i need , u have any Idea if i want to this Loss Amount as an evolution in a Line Chart ?

cuz it Shows me a weird Amount.

Thanks alot,

Re: Current and previous month

settu_periasamy — Thu, 06 Aug 2015 13:32:41 GMT

if you post the sample, then will try find out..