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: 
microland
Contributor III
Contributor III

Need help to calculate YTD in Qliksense Pivot

Hi, I am new to Qliksense. Need some logical help to calculate the YTD values of a sales module Plan and Actual like below,

         

Apr-16May-16Jun-16YTD Jun-16Achivement %
Sales PlanActualsSales PlanActualsSales PlanActualsSales PlanActuals
India1,0009892,0001,7003,0003,0006,0005,689105%
ME1,2001,2342,0001,7003,0003,0006,2005,934104%
UK/Europe1,0009892,0001,7003,0003,0006,0005,689105%
US1,0009892,0001,7003,0003,0006,0005,689105%
Australia1,0009892,0001,7003,0003,0006,0005,689105%
Grand Total5,2005,19010,0008,50015,00015,00030,20028,690105%

I am able to calculate the everything except YTD column. Please help.

7 Replies
Anonymous
Not applicable

Please share the script that you use to load this data into Qlik Sense

arulsettu
Master III
Master III

can you post the script how do you calculating

microland
Contributor III
Contributor III
Author

Hi Arul,

I am just doing simple pivot here. Rows - Geography, columns Month-Year and SalesType and Measures - SUM(Amount).

microland
Contributor III
Contributor III
Author

Hi,

Here is the script that i am using,

Load *, Month(Month) as "Fiscal Month",

  Monthname(Month) as "Month Year",

  Num(Monthname(Month)) as "MonthNo",

        Year(Month) as "Fiscal Year";

select [Geography], [Sub-Geo], [Team Leader], [Sales Owner],[Customer group],[Customer Name],

[Programs],[Project ID],[Project Description],[Business Unit],[Service Line],[Service Description],

[Service Category],[Revenue Category],[Type],[Month],[Currency],[Trxn Amount],[Trxn Rate],

[Amount (INR)],[Amount (USD)],[Amount (GBP)]

FROM "Sales".dbo."ModifiedRawDataExcel"

where Month<GETDATE();

miguelbraga
Partner - Specialist III
Partner - Specialist III

Hey there,

Is your Month field like a Date field? Can you please post what is the values of Month field?

Regards,

MB

microland
Contributor III
Contributor III
Author

Yes it is a date field. The value may look like this,

2016-07-15 00:00:00.000

2016-07-16 00:00:00.000

2016-07-17 00:00:00.000

2016-07-18 00:00:00.000

Anonymous
Not applicable

Hi

If you looking for YTD for 'Month-year' and  'Amount' check this

**Provided your 'Month-year' field is in  'MMM-YYYY' format **

The following will give Sum of last three months to up-to-date..

Date(Addmonths(Max(MonthYear), -3)..........................three months ago

Date((Max(MonthYear))..................................................this month ( or maximum month)

=Sum({$ <MonthYear={"$(='>=' & Date(Addmonths(Max(Month-Year), -3), 'MMM-YYYY') & '<=' & Date((Max(Month-Year)), 'MMM-YYYY'))"}>} Amount )