Solved: Re: Previous Month Data - Qlik Community

Anonymous · ‎2018-05-02

Hello Friends

i have a data where Month filed is there which has data like jan, feb, mar,.....dec

and Actuals

i want when filter select feb 2013 then i want

feb 2013 data and jan 2013 data

MON	Year	Actual
Jan	2013	5
Feb	2013	7
Mar	2013	9

so in above if filter select 2013 year and then select feb

then i want

current month previous month

7 5

juraj_misina · ‎2018-05-07

Hi,

from your script I can see that MON was a string value not a date field. Max() function does not work with strings. In general I recommend to use a "MonthID" concept where you create s sequence for every month period and work with that in your set analysis.

To create a MontID add this to your script:

Num#(YR)*12+Num(Month(Date#(MON, 'MMM'))     as MonthID

Then is expressions you can use

//current month
Sum({<Year, Month, MontID={$(=Max(MontID))}>} Actual) //turn off selections on all date fields and only select by MonthID
//previous month
Sum({<Year, Month, MontID={$(=Max(MontID)-1)}>} Actual)

Hope this helps.

Juraj

View solution in original post

eduardo_dimperio · ‎2018-05-03

Script or Chart?

juraj_misina · ‎2018-05-03

Hi,

In chart you can do this by using set analysis. For current month the expression would look something like this:

Sum({<MON={$(=Max(MON))}>} Actual)

Then for previous month

Sum({<MON={$(=Max(MON)-1)}>} Actual)

Remember that when using MON in set analysis in this context you cannot use MON as a chart dimension (otherwise previous month expression would be always equal to 0) and this will not work across years (so if you select january, then previous month will be 0 as well), but it should give you a starting point.

Hope this helps

Juraj

pawwy1415 · ‎2018-05-04

Hi,

Try this one

Current Month =Sum({<[MON]={'$(=Month(Max(Date)))'}>}Actual)

Previous Month =Sum({<[MON]={'$(=Month(AddMonths(Max(Date),-1))'}>}Actual)

Anonymous · ‎2018-05-04

THANKS For Reply But I had already done this and it is not working I don't know why

yesterday I made a change in script and made a new columns while loading a data

LOAD

MakeDate([YR],Month(Date#(Left([MON],3),'MMM'))) as Datefun,

MakeDate(Year(Date# (YR,'yyyy')), Month(Date#(MON, 'MMM')),Day(DATE#('01','dd'))) As FileDate,

num( month(date#(MON,'MMM'))) as MONTH

//date(Monthend(YR & '/' & MON & '/' & '01'), 'DD-MM-YYYY') As FileDate

FROM my excel sheet

I want previous month quarterly and Yearly trends in chart how can I or is there anything more I can do in script would also be fine.

I am unable to make create calendar measure to from this.

thanks.

eduardo_dimperio · ‎2018-05-04

Hi Juraj,

But how he will select a month not using MON as chart dimension?

juraj_misina · ‎2018-05-04

It can still be added into a filter pane (Qlik Sense) or listbox (QlikView) and selected from there.

eduardo_dimperio · ‎2018-05-04

But to add into a filter pane, that field need to be a Dimension, right?

zebhashmi · ‎2018-05-04

How are you Showing Current Month?

Same way you can use Month-1

juraj_misina · ‎2018-05-04

Not necessarily, but at least now I understand what you mean. The field can be defined as a master dimension on app level and can be used as dimension in other charts/filter panes. But if you want to use measure with set analysis in a chart, MON can not be used as a dimension in that particular chart.