Urngent Help in Pivot conversion in to qliksesne!!!

[srini]

I have a scenario like below in the attached file I have two parts one is source and another one is output. 

Source:

in the source there are two parts

one is raw data which is highlighted in grey.

Another one is derived using SUMIF from raw data to use in Pivot table.

Output

output is excel pivot which consist of Raw data and derived data. 

Question:

I need this to be achieved in Qlik Sense. I have tried with some cross table concept but not getting the exact value....

any steps to follow advise solutions? really frusturated 😞

Thanks!

[sunny_talwar]

Can you share an app where you have already loaded this data?

[srini]

here is the file

[sunny_talwar]

Something like this?

[srini]

Yes, but if you check first table(left side table) it is looking strange. it took twice M01, M02.... the Jan, Feb

[sunny_talwar]

This because of your script... try the new script

Data:
CrossTable(Month,Units,2)
LOAD Country,
    Category,
    "M01 Units",
    "M02 Units",
    "M03 Units",
    "M04 Units",
    "M05 Units",
    "M06 Units",
    "M07 Units",
    "M08 Units",
    "M09 Units",
    "M10 Units",
    "M11 Units",
    "M12 Units"
FROM [lib://model/Model file (3).xlsx]
(ooxml, embedded labels, header is 3 lines, table is source);
   
Test1:
LOAD *,
	 If(Category = 'ACTUAL' and MonthNum <= Num(Month(Today())), Units) as "YTD Actuals",
	 If(Category = 'FORECAST' and MonthNum <= Num(Month(Today())), Units) as "YTD FORECAST";
LOAD Country,
	 Category,
	 Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM')) as [Month],
	 Num(Month(Date#(Num(TextBetween([Month],'M',' Uni')),'MM'))) as [MonthNum],
	 Units 
Resident Data;
Drop Table Data;

[srini]

Hi Sunny,

very good day!

When use the same in my app it is not working properly.... it shows the same value for all rows also I don't think the filter pan working effectively. I couldn't see any changes in the figure when I check the filter pan option. I have attached the app for your reference. 

Thanks!

[sunny_talwar]

Before I dig any deeper.... is there a reason to have this table in your app?

[srini]

Yes Bro!

That is the main table... if you see some fields are used in the view as well from this table.

[sunny_talwar]

You mean Department, Team and Costline? Is there a reason you don't bring them in Test1 table and keep them in Data?