7 Replies Latest reply: Aug 16, 2016 10:04 AM by Gnanasekaran K

# 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-16 May-16 Jun-16 YTD Jun-16 Achivement % Sales Plan Actuals Sales Plan Actuals Sales Plan Actuals Sales Plan Actuals India 1,000 989 2,000 1,700 3,000 3,000 6,000 5,689 105% ME 1,200 1,234 2,000 1,700 3,000 3,000 6,200 5,934 104% UK/Europe 1,000 989 2,000 1,700 3,000 3,000 6,000 5,689 105% US 1,000 989 2,000 1,700 3,000 3,000 6,000 5,689 105% Australia 1,000 989 2,000 1,700 3,000 3,000 6,000 5,689 105% Grand Total 5,200 5,190 10,000 8,500 15,000 15,000 30,200 28,690 105%

• ###### Re: Need help to calculate YTD in Qliksense Pivot

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

• ###### Re: Need help to calculate YTD in Qliksense Pivot

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();

• ###### Re: Need help to calculate YTD in Qliksense Pivot

Hey there,

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

Regards,

MB

• ###### Re: Need help to calculate YTD in Qliksense Pivot

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

• ###### Re: Need help to calculate YTD in Qliksense Pivot

can you post the script how do you calculating

• ###### Re: Need help to calculate YTD in Qliksense Pivot

Hi Arul,

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

• ###### Re: Need help to calculate YTD in Qliksense Pivot

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..