Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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
h_eskelinen
Contributor III
Contributor III

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
Specialist III
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

galax_allu
Specialist
Specialist

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 )