topic Re: Suggest in QlikView

Suggest

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Suggest

satishqlik — Fri, 19 May 2017 04:22:26 GMT

How to load subcategory table in this situation???

Please help

Re: Suggest

prma7799 — Fri, 19 May 2017 05:29:14 GMT

Please share sample data

Re: Suggest

avinashelite — Fri, 19 May 2017 05:39:16 GMT

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

Re: Suggest

satishqlik — Fri, 19 May 2017 05:48:01 GMT

Hi Avinash,

I have shared my dummy data kindly look at

Re: Suggest

avinashelite — Fri, 19 May 2017 07:16:03 GMT

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

Re: Suggest

satishqlik — Fri, 19 May 2017 07:20:02 GMT

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

Re: Suggest

avinashelite — Fri, 19 May 2017 07:42:58 GMT

could you show me the sample output your excepting

Re: Suggest

satishqlik — Fri, 19 May 2017 08:33:52 GMT

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

Re: Suggest

avinashelite — Fri, 19 May 2017 09:53:45 GMT

Some thing like the attached ?

Re: Suggest

satishqlik — Fri, 19 May 2017 11:37:05 GMT

Almost closed avinash,

That would be great help if you can help me to get average as am expecting 150.

I wrote expression as for average Sum(value1)/Count(Distinct MonthYr).

Can you suggest me on this whether its correct or wrong???'

PFB for your reference!!!

Re: Suggest

avinashelite — Fri, 19 May 2017 11:59:40 GMT

check the attachment

Re: Suggest

avinashelite — Fri, 19 May 2017 12:00:03 GMT

Don't forget to mark the helpful and correct answers

Re: Suggest

satishqlik — Mon, 22 May 2017 07:13:05 GMT

Hi avinash,

Isn't work same logic for all the scenarios??

PFB for ur reference.

and also am trying to implemant same logic into my exsting data.

But not recognizing subprocess as a field(showing all records in the list box with gray color)

Sorry for my late replay.

is there anything wrong in my expression?Please suggest on this. !

Re: Suggest

avinashelite — Mon, 22 May 2017 07:23:23 GMT

share your actual app , will see that's the issue is

Re: Suggest

satishqlik — Mon, 22 May 2017 07:28:20 GMT

Hi Avinash,

Thanks for your quick replay,

am unable to share my actual application.

I have shared my sample(1234.qvw and 1234.xls) PFA for your reference!

In my actual application why its showing in gray color??

same logic I have followed as you suggested.

Re: Suggest

avinashelite — Mon, 22 May 2017 07:37:23 GMT

Gray color ?? I am not getting what your trying to say ...the data might not be related ? or your talking about the total showing in grey ?

Re: Suggest

satishqlik — Mon, 22 May 2017 07:42:27 GMT

irrespective of related or non related,

By default total showing in the list box with gray color..

Re: Suggest

avinashelite — Mon, 22 May 2017 07:44:01 GMT

share the screen shoot ? you mean the aggr tables total is showing Grey ?

Re: Suggest

satishqlik — Mon, 22 May 2017 08:59:05 GMT

PFA for your reference!!