Suggest

[satishqlik]

Hi,

Please suggest on my scenario.

load

data2

from<>

Left Join(Data3)

Load Distinct *,Dual(Cal_Year-1 &'/'& Cal_Year, Cal_Year) as FYear,       

         Dual(Month, fMonth) as Cal_Month,

         Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3))as Cal_Quarter;

LOAD Distinct*,Year + If(Month>=$(vFiscalYearStartMonth), 1, 0) as Cal_Year,

Mod(Month-$(vFiscalYearStartMonth), 12)+1 as fMonth;

LOAD Year([Month-Yr]) as Year,

  Month([Month-Yr]) as Month,

     Category,

     0 as [Training Days (in week)],

     num([Total Revenue (in lakhs)]*100000) as [Total Revenue],

     num([Revenue per HC (in Lakhs)]*100000) as [Average Revenue],

     num([Recruitment Cost per month]*100000) as [Recruitment Cost],

     [Average Annual CTC per head(in Lakhs, Billable & nonBillable)] as [Annual CTC],

     HC as Revenue_HC,

     [CTC Variance],

     [Salary Cost]

FROM

<>;

I have showed dashboard as per categories wise.

Now I have recieved another table with subcategory field with partial sums

i.e.,Category of value=4SubCategories of values(100=25+25+25+25)

I want to show dashboard as it is even after load the subcategory table.

But If I load subcategory table values are not matching.

Any ideas???

[satishqlik]

How to load subcategory table in this situation???

Please help

[prma7799]

Please share sample data

[avinashelite]

Share the sample app with the data so that it will be helpful for us to understand the issue

[satishqlik]

Hi Avinash,

I have shared my dummy data kindly look at

[avinashelite]

You have the table1 with aggr count and another table with detail data so you could calculate the aggr from details table rite ? why you need aggr table ? if you still want both the tables join the tables that should resolve the issue

[satishqlik]

If I use join then sum and avg results are not coming what exactly am looking for ....

[avinashelite]

could you show me the sample output your excepting

[satishqlik]

If I have 2 tables(process and sub process)

This is what am looking for

process	Sub Process	MonthYr	Sum(value1)
clientA	a1	01-Feb-17	25
clientA	a2	01-Feb-17	25
clientA	a3	01-Feb-17	25
clientA	a4	01-Feb-17	25
clientA	a1	01-Mar-17	50
clientA	a2	01-Mar-17	50
clientA	a3	01-Mar-17	50
clientA	a4	01-Mar-17	50
total			300

[avinashelite]

Some thing like the attached ?