Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

jood_ahmad
Contributor II

Month & Previus Month

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

17 Replies
Partner
Partner

Re: Month & Previus Month

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

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 try this and this is not worked, I need only to present the value no need to do Sum.

Partner
Partner

Re: Month & Previus Month

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

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

Re: Month & Previus Month

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

Highlighted
Partner
Partner

Re: Month & Previus Month

teste2.gif

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

Re: Month & Previus Month

ITEM_CODECurrent MonthPrevius Month
COLUT0001240240
PMPBA0005350350
RMADT001328.60952951.222157
RMADT0014723.386042716.25148
RMADT002329.06160629.052239
RMADT0027117.35559118.465331
RMADT004266.34164.713
RMDPT0004146.45918180.9
RMGPT000927.999999187.5
RMOLT000211627.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]) 

Partner
Partner

Re: Month & Previus Month

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.

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

Re: Month & Previus Month

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_CODEMonth1Price of RM in Primary UOM
COLUT0001Jan240
PMPBA0005Jan350
RMADT0006Jan
RMADT0013Jan51.222157
RMADT0014Jan716.25148
RMADT0023Jan29.052239
RMADT0027Jan118.465331
RMADT0042Jan64.713
RMADT0047Jan180.9
RMDPT0004Jan187.5
RMGPT0009Jan27.999996
RMOLT0002Jan116
SFCRF0006Jan
COLUT0001Feb240
PMPBA0005Feb350
RMADT0006Feb
RMADT0013Feb28.609529
RMADT0014Feb723.386042
RMADT0023Feb29.061606
RMADT0027Feb117.35559
RMADT0042Feb66.341
RMDPT0004Feb146.45918
RMGPT0009Feb27.999999
RMOLT0002Feb116
SFCRF0006Feb
jood_ahmad
Contributor II

Re: Month & Previus Month

I add the QVW sample