Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
konidena
Creator
Creator

Current Month sales and previous month sales caluculation

Hi Team,

I have one "Full_Date" column as

20000101

20000102

20000103......like this i have till 20251231. It is not of type date. it is just stored as string in database with varchar(8).

I made a list box for month and date  using the Full_Date column. Below the way i followed.

in General tab of list box, in the field dropdown, i have selected "<Expression>" and  entered the expression as below.

=if(left(right(Full_Date,4),2)=1, 'Jan',

if(left(right(Full_Date,4),2)=2, 'Feb'.....like this i have created all 12 months

=if(right(Full_Date,2)=1,1,

if(right(Full_Date,2)=2, 2....like this i created numbers 31 for date field.

so, i have month field, date field.

Question:

I need to create a pivot table here. Columns are

Dimension is Region

Expressions: Current Month sales, Current Month Target, Previous month sales, Previous month target..

Here, whatever month i have selected from the month field is my current month.

How to keep a month using set analysis in current month sales expression, previous month sales expression.

I have created month and date fields but those are just expressions, not available in the fields list.

How to make this month expression as month field and date expression as date field? Please suggest.

Note: Date conversion function (Date#) didn't workout for this scenario.

12 Replies
marcus_sommer

I think date#() will work, see this for your first date-value:

=date(date#(20000101, 'YYYYMMDD'), 'DD.MM.YYYY')

and then you should use a master-calendar to link these date to period-fields like month and year. Maybe these approach needs this or that adjustment in relation to your data but in each case it's better than your heavly nested if-loop approach.

Here you could find more about: How to use - Master-Calendar and Date-Values

- Marcus

swuehl
MVP
MVP

You should really look into parsing your field values as dates, then create a master calendar, or at least create calendar fields from your date. If you have create fields for your calendar, then you can easily apply set analysis (which is operating on field values):

LOAD *,

          Date(Full_Date) as Date,

          Month(Full_Date) as Month,

          Year(Full_Date) as Year;

LOAD

     Date#(Full_Date, 'YYYYMMDD') as Full_Date,

     ...

FROM YourTable;   

See also

The Master Calendar

Why don’t my dates work?

Set Analysis for certain Point in Time

HirisH_V7
Master
Master

Hi,

check this,

Data:

LOAD *,

Date(Date#(SysDate,'YYYYMMDD'),'DD/MM/YYYY') as Date,

//Mid(SysDate,7,8)&'/'&left(Mid(SysDate,5,6),2)&'/'&Mid(SysDate,1,4) as Date,

Month(Date(Date#(SysDate,'YYYYMMDD'),'DD/MM/YYYY')) as MonthName,

Num(Month(Date(Date#(SysDate,'YYYYMMDD'),'DD/MM/YYYY'))) as MonthNum

INLINE [

    Region, SysDate, Sales

    State 1, 20150130, 88

    State 1, 20150130, 585

    State 2, 20150228, 5245

    State 2, 20150228, 54

    State 3, 20150228, 577

    State 3, 20150328, 54

    State 1, 20150328, 77

    State 1, 20150328, 888

    State 2, 20150228, 99

    State 1, 20150328, 66

    State 2, 20150328, 77

    State 3, 20150130, 78

    State 1, 20150130, 52

    State 1, 20150228, 366

    State 1, 20150130, 525

    State 1, 20150130, 74

    State 1, 20150228, 411

    State 1, 20150130, 44

];

By using this fields ,at front end:

Current month and Previous month-203452.PNG

Current Month sales Expression:    =Sum({<MonthNum={'$(=Max(MonthNum))'}>}Sales)

Previous Month sales Expression:  =Sum({<MonthNum={'$(=Max(MonthNum)-1)'}>}Sales)

Current Month sales According to data  of today Expression:    =

                SUM({<DateField ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Sales)


Previous Month sales According to data  of today Expression:  =

                SUM({<DateField ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)

Hope this helps,

Regards,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
rodri_morales
Creator II
Creator II

There is a problem with your expression. They always show you the maximum month and the previous. Maximum month could not be the current month.

To select current month you have to use the function Today(). for example: Num(Month(Today()))

May be you could use a variable like: vCurrentMonth = Num(Month(Today()))

and another variable to the previous month: vPreviousMonth = $(vCurrentMonth)-1

jagan
Luminary Alumni
Luminary Alumni

Hi Srinivas,

First format the date in script like below then it would be easier to perform this type of operations.

Data:

LOAD

*,

Month(Full_Date_Format) AS Month,

Year(Full_Date_Format) AS Year;

LOAD

*,

Date(Date#(Full_Date, 'YYYYMMDD')) AS Full_Date_Format

FROM DataSource;


Now in front end use below expressions


Current Month Sales: Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>} Sales)

Previous Month Sales:Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))'}>} Sales)


Current Month Target: Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>} Target)

Previous Month Target:Sum({<Year=, Month=, Full_Date_Format={'>=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))'}>} Target)


Hope this helps you.


Regards,

Jagan.

konidena
Creator
Creator
Author

For all my earlier reports, we used the date format which is mentioned in the environment variables of the script window.

Is there any possibility to convert my date string into the date format which is same to Date format in environmental variables.

Date(Date#(Full_Date,'YYYYMMDD'),'MM/DD/YYYY')

Is my above Code rite? will it work?

konidena
Creator
Creator
Author

This is my script

LOAD Calendar_Month,

     Calendar_Year,

     Date_Key,

     Full_date,

     Date(Date#(Full_date,'YYYYMMDD'),'MM/DD/YYYY') as FullDate,

     Month(FullDate) as Month,

     Date(FullDate) as Date,

     Year(FullDate) as Year,

     Qtr

from

time.qvd;

when executing the script, it is saying FullDate field not found. Please suggest

HirisH_V7
Master
Master

Hi,


Yeah i thought according to data he need the output.Thanks for update.

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
marcus_sommer

I think you have here a typo on FullDate then in your script are three different versions of them which seems unlikely.

- Marcus