Grouping by parameters

[#1]

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.

[daveamz]

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

[mightyqlikers]

hi

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

give sample data in excel.

Regards

$@

[#4]

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

[tresesco]

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;

[Anonymous]

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

[#7]

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

[#8]

Tried it didnt work

[settu_periasamy · Accepted solution]

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;

[#10]

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