Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning,
I am new to QV and need some help with filterring strings in a formula:
Given is the data in the following table: TB01_20140902_101356.xls
which shows the turnover and qty. per material and product hierache.
I would like to sum the turnover with the following formulas:
1. if (Produkthierarchie = 'X?*' , 'Y?*',sum(Nettoumsatz))
2. if (Produkthierarchie like 'GATE*' , 'ACTR*',sum(Nettoumsatz))
but both deliver the same result? Could you please help me resolving this?
Thanks, Matthias
Hi Jagan,
first of all thanks for your help.
What do I expect:
the total turnover for the month of July for example was:
The field "Teile" is calculatet by
sum ({<Produkthierarchie ={ 'X?*' , 'Y?*'}>}Nettoumsatz)
"Anlagen" by
sum ({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)
"Nettoumsatz" by
sum (Nettoumsatz)
Since all Materials have a prod. hierach. which is either
X
Y Y
GATE*
ACTR*
the sum of
X
Y Y
and
GATE*
ACTR*
should be
sum (Nettoumsatz)
but it is not, see table.
Try like:
=Sum( if (WildMatch(Produkthierarchie = 'X?*' , 'Y?*'), Nettoumsatz))
Thanks for your answer, but this returns 0 as the result. my formula returned the total turnover.
Hi,
Try below set analysis expressions
1. sum({<Produkthierarchie ={ 'X?*' , 'Y?*'}>}Nettoumsatz)
2. sum({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)
Hope this helps you.
Regards,
jagan.
Correction: replace '=' with a comma and try like:
=Sum( if (WildMatch(Produkthierarchie , 'X?*' , 'Y?*'), Nettoumsatz))
Hi,
What is ? in your search string? It is there in your value? If it is in value then expressions in my previous reply works
or try below
1. sum({<Produkthierarchie ={ 'X*' , 'Y*'}>}Nettoumsatz)
2. sum({<Produkthierarchie ={ 'GATE*' , 'ACTR*'}>}Nettoumsatz)
Regards,
Jagan.
Try this .. Sum( if (WildMatch(Produkthierarchie,'X?*','Y?*'), Nettoumsatz))
Hi,
Try like this
Sum(if(wildmatch(Produkthierarchie,'X?*' , 'Y?*'),Nettoumsatz))
Regards,
Hi Jagan,
the set analysis expressions at leased deliverred a result, but I think it is not correct.
I have checked that all records in my table contain a prod. Hierach. so the sum of both sesults sould deliver the total turnover, but they do not.
The '?' in the formula is because I do not know at which plave the second 'Y' is thin the prod. hierach., the content of that field can be:
1. X
2. Y Y
3. GATE XXXX XXXX
4. ACTR XXXX XXXX, like you see in the attached Excel.
If I take the '?' out of your formular it returns '0'
Thanks.
Hi,
You have four spaces in the Excel, so try this expression
1. sum({<Produkthierarchie ={ 'X*' , 'Y????Y'}>}Nettoumsatz)
Hope this helps you.
Regards,
Jagan.