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

[qlikview979]

Attachment missed

[satishqlik]

Snapshot which I have attached with 234.PNG in the above.

[satishqlik]

Avinash,

am not talking about aggr() results.

If I select sub process in the list box it is showing with gray color

Could you please look at there???

[avinashelite]

yes , why is that because the grey ed out values are aggr values which are calculated not considering the sub-process . Eg: if you have aggr value 2000 which is the sum of all the sub-process values ,,,if you want to tag 2000 then to which sub-process you will tag??

[satishqlik]

So isn't part of the datamodel????

I mean to ask subprocess field is not the part of the process table(main table)???

if yes, how to overcome from this???

Is it possible??

Please suggest if it is possible.

I have derived Identifier in the both tables(process and sub process tables).here am taking with out single codes like this

table1:

load

process,

date,

value ,

process as Identifier

From <>;

Join

table2:

Load

process,

Subprocess,

date,

value,

Subprocess as identifier

From <>;

which one is correct with single codes as you suggested or without single codes???

[avinashelite]

its the part of main process table but the sub-process values will be blank for this ....

how to overcome from this???

ok, in the 2000 aggr value which sub-process you will populate ? if you populate sub-process then it could wrong rite ?

in your case you should identify the gaps i.e if you main table has Process and you second table has process+sun-process then main table could show the aggrr on the process which is not the case with your data .

As per me you don't need the aggr table at all because your detail table (which has the sub-process) has more details and you could calculate the sum from that

Hope this makes you clear

[satishqlik]

Hi Avinash thanks for your time.

Let me understand the final conclusion.

I have derived Identifier in the both tables(process and sub process tables).

here am taking with out single codes like this

table1:

load

process,

date,

value ,

process as Identifier

From <>;

Join

table2:

Load

process,

Subprocess,

date,

value,

Subprocess as identifier

From <>;

I want to understand

Which one is make sense(string or field)??

[avinashelite]

Don't get confuse

process as Identifier

this is just a identifier to identify the table s not the data values

[satishqlik]

am not able to find out any difference if I use with single codes or without single codes.

but In my actual application,I have used identifier with out single codes.

am not getting any error in my actual application but in sample file am getting error as field is not found.

which one is make sense???

Please suggest as am totally struck here.

PFB.

[avinashelite]

Hmm I am not talking about the single codes .......

Answer my question

2000 aggr value which sub-process you will populate ? its Aggr value of all the Sub Process rite