Month & Previus Month

[jood_ahmad]

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

[mario_sergio_ti]

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]) 

[mario_sergio_ti]

Friend. It worked?

[jood_ahmad]

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

[mario_sergio_ti]

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.

[jood_ahmad]

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

[mario_sergio_ti]

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

[mario_sergio_ti]

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.

[mario_sergio_ti]

Got it?