Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
apegahfar
Contributor III
Contributor III

compare sum of sale with previous period

Hi Community

My test_File is attached

I wanna compare sum of sale by month from previous month to show in a text box

Regard

5 Replies
sunny_talwar

I suggest creating a new field in the script like this

Test:

LOAD *,

Date(MonthStart(Date#(Year&Month, 'YYYYMMM')), 'MMM-YYYY') as MonthYear;

LOAD * INLINE [

    ID, Year, Month, Sale

    1, 2015, Mar, "1,000"

    2, 2015, Mar, "1,500"

    3, 2015, Mar, "1,000"

    4, 2015, Mar, "2,000"

    5, 2015, Mar, "1,750"

    6, 2015, Apr, "2,200"

    7, 2015, Apr, "2,350"

    8, 2015, Apr, "2,571"

And then use this expression

=Sum({<Year, Month, MonthYear = {"$(=Date(AddMonths(Max(MonthYear), -1), 'MMM-YYYY'))"}>}Sale)

apegahfar
Contributor III
Contributor III
Author

Dear Sunny Talwar

thank you for your reply

it works well

but imagine we cant create such field how we can solve that ?

I mean we cant use Addmonth function ( it doesnt support my language (Persian))

regard

sunny_talwar

AddMonths is a QlikView function... I am sure there will be similar function for it in Persian version of the application.

Also, you can do this without manipulation in the script... but when January is selected which year's December would you want to see? This year or last year?

apegahfar
Contributor III
Contributor III
Author

I know but in fact I don't have English_month and English_Year Fields

instead of that I have Persian_month and Persian_Year Fields

also , there is Period Code ( 1 , 2 , 3 , .... )

can this field help us ?

Also, you can do this without manipulation in the script... but when January is selected which year's December would you want to see? This year or last year?

absolutely this year

thanks

ravikumar_iyana
Creator
Creator

Hi Amirhossein,

Like this Year wise Comparison purpose i recommended to take Straight Table:

In Dimension take Region

In Expression take 2:

     Expression 1:  Label: ='YTD'&chr(10)& 'Sales ' & Max(Year)

                             Expression: sum({< Year={$(=max(Year))}  >}Actual) 

     Expression 2: Label: ='YTD'&chr(10)& 'Sales ' & Max(Year-1)

                              Expression: sum({<Year={$(=max(Year)-1)}>}Actual)


U can try this it will works