topic Re: Grouping by parameters in QlikView

Grouping by parameters

Fri, 04 Mar 2016 09:47:20 GMT

How do i group by Month and Year same time. See attached Only Year is associated with the Total When I click on Month the Total disappears.

Re: Grouping by parameters

daveamz — Fri, 04 Mar 2016 10:31:33 GMT

Hi,

You can use:

Sum({<Text = {'*'}-{'*total*'}>}Data)+

Sum({<Text = {'*total*'}, Months=>}Data)

But when you will select one month, the values for cost, Depreciate, equip....will be only for that month an the totals will be for the whole year.

Regards,

David

Re: Grouping by parameters

mightyqlikers — Fri, 04 Mar 2016 10:33:17 GMT

there is issue in your data. thats y it is not displaying totals when u select month.

give sample data in excel.

Regards

Re: Grouping by parameters

Fri, 04 Mar 2016 11:13:51 GMT

No there is no issue with the data. Total was brought in let me say calculatedly from the backend

Re: Grouping by parameters

tresB — Fri, 04 Mar 2016 11:27:55 GMT

While summing the data in the script group it by two fields like:

Load

'Total' as Text,

Year,

Month,

Sum(Data) as Data

Resident T Group By Year, Month;

Re: Grouping by parameters

Anonymous — Fri, 04 Mar 2016 11:28:17 GMT

Total is getting disappeared becoz it is not associated with your Month field....

Check the attachment....

Also, your text of total is coming in Right which means its in number format not into string

Re: Grouping by parameters

Fri, 04 Mar 2016 14:27:33 GMT

Hi,

Can you please try by concatenating Year and Month like '201601', 201602, .. and you do group by based on this concatenated column.

Thanks,

Sreeman

Re: Grouping by parameters

Fri, 04 Mar 2016 16:16:19 GMT

Tried it didnt work

Re: Grouping by parameters

settu_periasamy — Fri, 04 Mar 2016 16:41:42 GMT

Hi,

Try this in your script..

T:
LOAD No,
Text,
Year,
Months,
Data
FROM
[0215_184014.xls]
(biff, embedded labels, table is Sheet1$);

STORE T into T.qvd(qvd);

NoConcatenate
Final:
Load *,
1 as SORT
Resident T
Where WildMatch(No, '*14*', '*20*', '*21*','*22*', '*36*','*24*');

Concatenate(Final)
Load Dual ('Total equipment:',1) as Text,
Year,
Sum(Data) as Data,
No,
Months,
2 as SORT
RESIDENT T
where WildMatch(No, '*14*', '*20*', '*21*','*22*', '*36*','*24*')
Group By Year,
No,
Months;

Concatenate(Final)
Load *,
3 as SORT
Resident T
Where WildMatch(No, '*26*', '*27*', '*28*');

Concatenate(Final)
Load Dual ('Total Work:',2) as Text,
Sum(Data) as Data,
Year,
No,
Months,
4 as SORT
RESIDENT T
where wildmatch(No, '*26*', '*27*', '*28*')
Group By Year,
No,
Months;

Concatenate(Final)
Load *,
5 as SORT
Resident T
Where WildMatch(No, '*26*', '*27*', '*28*', '*14*', '*20*', '*21*','*22*', '*36*','*24*');

Concatenate(Final)
Load Dual ('TOTAL:',3) as Text,
Sum(Data) as Data,
Year,
No,
Months,
6 as SORT
RESIDENT T
Where WildMatch(No, '*26*', '*27*', '*28*', '*14*', '*20*', '*21*','*22*', '*36*','*24*')
Group By Year,
No,
Months
Drop Table T;

Re: Grouping by parameters

Fri, 04 Mar 2016 16:56:20 GMT

It would be helpful to provide me the dummy data and with the expected output.