topic Re: Month & Previus Month in QlikView

Month & Previus Month

jood_ahmad — Tue, 14 Mar 2017 13:03:32 GMT

Dears,

if I have a column called Price and the Price for each month is different than the next month and I need to present in Pivot table this column for the current month and the previous month beside each other.

how can I do that.

thank you for support.

stevedark‌

Message was edited by: ahmad kastero

Re: Month & Previus Month

mario_sergio_ti — Tue, 14 Mar 2017 13:08:34 GMT

Friend;

In the dimension of the pivoting table, place the Months

Current Year Expression:

Sum({<[Year]={'$(=Max([Year]))'}>} [Field])

Previous Year Expression:

Sum({<[Year]={'$(=Max([Year])-1)'}>} [Field])

Re: Month & Previus Month

jood_ahmad — Tue, 14 Mar 2017 13:15:22 GMT

thank you Mario, but I try this and this is not worked, I need only to present the value no need to do Sum.

Re: Month & Previus Month

mario_sergio_ti — Tue, 14 Mar 2017 13:27:23 GMT

How is your expression? Can you put a printe of your PivotTable?

Re: Month & Previus Month

Anonymous — Tue, 14 Mar 2017 13:27:45 GMT

Can you share a sample of your requirement. in excel or others.

Re: Month & Previus Month

mario_sergio_ti — Tue, 14 Mar 2017 13:32:28 GMT

Re: Month & Previus Month

jood_ahmad — Tue, 14 Mar 2017 13:42:36 GMT

ITEM_CODE	Current Month	Previus Month
COLUT0001	240	240
PMPBA0005	350	350
RMADT0013	28.609529	51.222157
RMADT0014	723.386042	716.25148
RMADT0023	29.061606	29.052239
RMADT0027	117.35559	118.465331
RMADT0042	66.341	64.713
RMDPT0004	146.45918	180.9
RMGPT0009	27.999999	187.5
RMOLT0002	116	27.999996

when I try to get this result it give me 0

and this is my expression :

only({<Month={"$(=Max(Month)-1)"}>} [Price of RM in Primary UOM])

and I try this :

vIgnoreAllDates: to ignore all date selection .

Sum({<vIgnoreAllDates,Month={"$(=Max(Month)-1)"}>} [Price of RM in Primary UOM])

Re: Month & Previus Month

mario_sergio_ti — Tue, 14 Mar 2017 14:07:58 GMT

Follow:

    Sum({$< [Year]={$(=Year(AddMonths(Max([Date]),-1)))},
              [Month]={$(=Month(AddMonths(Max([Date]),-1)))}
    >} [Price of RM in Primary UOM])

If necessary, change the [Date] field name in the above expression;

-1 for previous month referring to the maximum date in the data model;

For previous months, only change from 1 to the desired one.

Recommend avoiding renaming of field names equal to the names of Qlik native functions.

Re: Month & Previus Month

jood_ahmad — Tue, 14 Mar 2017 14:25:29 GMT

I try to upload sample from the data in excel file but I don't know why it is not uploaded, but her is sample for two months

ITEM_CODE	Month1	Price of RM in Primary UOM
COLUT0001	Jan	240
PMPBA0005	Jan	350
RMADT0006	Jan
RMADT0013	Jan	51.222157
RMADT0014	Jan	716.25148
RMADT0023	Jan	29.052239
RMADT0027	Jan	118.465331
RMADT0042	Jan	64.713
RMADT0047	Jan	180.9
RMDPT0004	Jan	187.5
RMGPT0009	Jan	27.999996
RMOLT0002	Jan	116
SFCRF0006	Jan
COLUT0001	Feb	240
PMPBA0005	Feb	350
RMADT0006	Feb
RMADT0013	Feb	28.609529
RMADT0014	Feb	723.386042
RMADT0023	Feb	29.061606
RMADT0027	Feb	117.35559
RMADT0042	Feb	66.341
RMDPT0004	Feb	146.45918
RMGPT0009	Feb	27.999999
RMOLT0002	Feb	116
SFCRF0006	Feb

Re: Month & Previus Month

jood_ahmad — Tue, 14 Mar 2017 14:44:41 GMT

I add the QVW sample

Re: Month & Previus Month

mario_sergio_ti — Tue, 14 Mar 2017 14:53:51 GMT

It looks like you do not have a date field or numeric month;

Then I had to convert and adapt the expression, it follows;

Sum({$< [Month1]={$(=Capitalize(Date(Max(Date#([Month1],'MMM')-1),'MMM')))}  
    >} [Price of RM in Primary UOM])

Re: Month & Previus Month

mario_sergio_ti — Tue, 14 Mar 2017 18:23:50 GMT

Friend. It worked?

Re: Month & Previus Month

jood_ahmad — Wed, 15 Mar 2017 06:33:27 GMT

I try to do it but it give me 0 for all. can you share the QWV please.

Re: Month & Previus Month

mario_sergio_ti — Wed, 15 Mar 2017 11:30:19 GMT

I work with Qlik Sense (.QVF)

But replicate my logic perfectly in Qlikview;

In the script:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET ThousandSep=',';
SET DecimalSep='.';

Amostra:
LOAD * Inline [
ITEM_CODE Month1 'Price of RM in Primary UOM'
COLUT0001 Jan 240
PMPBA0005 Jan 350
RMADT0006 Jan
RMADT0013 Jan 51.222157
RMADT0014 Jan 716.25148
RMADT0023 Jan 29.052239
RMADT0027 Jan 118.465331
RMADT0042 Jan 64.713
RMADT0047 Jan 180.9
RMDPT0004 Jan 187.5
RMGPT0009 Jan 27.999996
RMOLT0002 Jan 116
SFCRF0006 Jan
COLUT0001 Feb 240
PMPBA0005 Feb 350
RMADT0006 Feb
RMADT0013 Feb 28.609529
RMADT0014 Feb 723.386042
RMADT0023 Feb 29.061606
RMADT0027 Feb 117.35559
RMADT0042 Feb 66.341
RMDPT0004 Feb 146.45918
RMGPT0009 Feb 27.999999
RMOLT0002 Feb 116
SFCRF0006 Feb
] (delimiter is ' ');

In the layout, create a table and place the dimension ITEM_CODE;

Measures/expression:

//Last month:

Sum({$< [Month1]={$(=Capitalize(Date(Max(Date#([Month1],'MMM')-1),'MMM')))}
    >} [Price of RM in Primary UOM])

//Current month:

Sum({$< [Month1]={$(=Capitalize(Date(Max(Date#([Month1],'MMM')),'MMM')))}
    >} [Price of RM in Primary UOM])

It worked well here.

Re: Month & Previus Month

jood_ahmad — Wed, 15 Mar 2017 11:58:40 GMT

thank you Mario but I did what you said and it is not working , could you please upload that document if it possible

Re: Month & Previus Month

mario_sergio_ti — Wed, 15 Mar 2017 12:03:32 GMT

Knowing that the script and the expression of Qlik Sense and Qlikview are practically the same, create a file by Qlikview (.QVW);

Re: Month & Previus Month

mario_sergio_ti — Wed, 15 Mar 2017 13:05:47 GMT

I was able to open your QVW in my Qlik Sense (script only), my Qlikview that I installed is Personal Edition and does not allow more

I noticed that you have a calendar in data modeling, and I found the DATE_KEY field of data type;

Then the solution follows:

//Last month:

    Sum({$< [Year]={$(=Year(AddMonths(Max([DATE_KEY]),-1)))}, 
              [Month1]={$(=Month(AddMonths(Max([DATE_KEY]),-1)))} 
    >} [Price of RM in Primary UOM])

//Current month:

    Sum({$< [Year]={$(=Year(Max([DATE_KEY])))}, 
              [Month1]={$(=Month(Max([DATE_KEY])))} 
    >} [Price of RM in Primary UOM])

Notice that it is the same solution that I mentioned above, where I asked to change the [Date] by the date field of your data modeling.

Re: Month & Previus Month

mario_sergio_ti — Thu, 16 Mar 2017 18:36:59 GMT

Got it?