Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Re: Month & Previus Month

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.soares@cubotimize.com | WhatsApp (21) 97014-5133 | Eu trabalho com paixão!
Partner
Partner

Re: Month & Previus Month

Friend. It worked?

mario.soares@cubotimize.com | WhatsApp (21) 97014-5133 | Eu trabalho com paixão!
jood_ahmad
Contributor II

Re: Month & Previus Month

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

Partner
Partner

Re: Month & Previus Month

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.

mario.soares@cubotimize.com | WhatsApp (21) 97014-5133 | Eu trabalho com paixão!
jood_ahmad
Contributor II

Re: Month & Previus Month

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

Partner
Partner

Re: Month & Previus Month

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

mario.soares@cubotimize.com | WhatsApp (21) 97014-5133 | Eu trabalho com paixão!
Partner
Partner

Re: Month & Previus Month

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.soares@cubotimize.com | WhatsApp (21) 97014-5133 | Eu trabalho com paixão!
Partner
Partner

Re: Month & Previus Month

Got it?

mario.soares@cubotimize.com | WhatsApp (21) 97014-5133 | Eu trabalho com paixão!