Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
I am able to calculate the everything except YTD column. Please help.
Please share the script that you use to load this data into Qlik Sense
can you post the script how do you calculating
Hi Arul,
I am just doing simple pivot here. Rows - Geography, columns Month-Year and SalesType and Measures - SUM(Amount).
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();
Hey there,
Is your Month field like a Date field? Can you please post what is the values of Month field?
Regards,
MB
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
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 )