topic Re: Add previous month to selection in QlikView

Add previous month to selection

Thu, 25 Oct 2012 12:13:47 GMT

hi!!

i v got tu do a graphic and chart of sells by month

but i want to compare previous month to selected month always

i do this :

=sum({$<month={$(=AddMonths(month,-1))}>} sells)

but it doesnt work

obviously because for exemple then i selected sptember there s no month agust in selected records, there s only september records !

how can i do that ???

Re: Add previous month to selection

Thu, 25 Oct 2012 12:39:15 GMT

Hi king-greg.

You can do that using variables... See the file

Reggards,

Bruno

Re: Add previous month to selection

Thu, 25 Oct 2012 13:49:02 GMT

it doesnt work

i create a variable : vtest=Date(AddMonths(month, -1))

i put

=sum( {$<month= {$(vtest)}>} MCD_B )

an nothing appeared!!

Re: Add previous month to selection

MayilVahanan — Thu, 25 Oct 2012 13:57:06 GMT

Try like this

in variable, Month(AddMonths(Max(Date),-1)) gives Aug,if max(Date) equal to today

Re: Add previous month to selection

Gysbert_Wassenaar — Thu, 25 Oct 2012 14:23:12 GMT

Addmonths works with by adding a number of months to a date.

Try =sum({$<month={$(=Only(month)-1))}>} sells)

Re: Add previous month to selection

Mon, 29 Oct 2012 13:23:17 GMT

it doesn wotks

i dont know how to do that

get 2 selections at a time !

Re: Add previous month to selection

Mon, 29 Oct 2012 13:25:30 GMT

King, what's your Qlikview Server Version?

Re: Add previous month to selection

Mon, 29 Oct 2012 13:29:27 GMT

Re: Add previous month to selection

Mon, 29 Oct 2012 13:34:24 GMT

Create a variable "VAR_PreviousMonth" whit this expression:

=Num(Month(AddMonths(YourDateFieldHere, -1)))

And, next, in your graph's expressions, try this

Sum({<YourMonthFieldHere={'$(VAR_PreviousMonth)'}>} YourValueFieldHere)

Good Luck!

Bruno Oliveira

Re: Add previous month to selection

MayilVahanan — Mon, 29 Oct 2012 13:39:05 GMT

Try like this

For Current month

=Sum({<DateFieldName={"=$(=Max(DateFieldName))"}>} Sales)

For Previous month:

=Sum({<DateFieldName={"$(=Addmonths(Max(DateFieldName), -1))"}>}Sales)

If not satisfy your requirement, can you post a sample file

Hope this helps you.

Re: Add previous month to selection

Mon, 29 Oct 2012 14:03:23 GMT

nothing append!!

my date date_ma is in 'MM-YYYY' format

i create a variable vDatemun : =date(AddMonths(date_ma, -1),'MM-YYYY')

i create a grpahic with no dimension and 2 expressions:

1) =Sum({<date_ma={"=$(=Max(date_ma))"}>} MCD_B)

2)=Sum({<date_ma={'=$(vDatemun)'}>}MCD_B)

when i select september, it displays the same total of september !

Re: Add previous month to selection

Mon, 29 Oct 2012 15:35:52 GMT

If your date format is 'MM-YYYY', something like this should work:

Create a variable "VAR_PreviousMonth" whit this expression:

=Date(AddMonths(YourDateFieldHere, -1), 'MM-YYYY')

And, next, in your graph's expressions, try this

Sum({<YourMonthFieldHere={'$(VAR_PreviousMonth)'}>} YourValueFieldHere)

Good Luck!

Bruno Oliveira

Re: Add previous month to selection

kaicpa2 — Mon, 29 Oct 2012 16:54:31 GMT

Hello King-greg

I had the same problem and solved in this way

Legenda

Period = date gained from a excel file like a number

Qty = is the dimension I want to sum up. For you is sells

First,I have created a variable:

vMonthDelta= date(Period,'DD/MM/YYYY')- MonthStart(Period,-1 )

Then in the chart expression:

Current month : Sum({$<Period ={$(=( Period))} >} Qty)

Previous month: Sum({$<Period ={$(=( Period)-vMonthDelta)} >} Qty)

see if it works in this way

Carlo

Re: Add previous month to selection

Mon, 29 Oct 2012 18:00:37 GMT

If you can modify your data model you could do something like:

SalesPeriods:

Load MaxString(Period) as Period,

RowNo() as PeriodRank

Resident SalesData

group by Period

order by Period desc;

Now your sales data periods are ranked so instead of comparing to dates you can use the current sales period based on rank of 1, and the previous sales period would be rank of 2 even if it was really several months in the past.

Not sure if that will help you or if your situation is much more complicated than just having missing months of data.