Set Expression with different modifiers for the same field

[#1]

Dear Community,

my data contains sales to different Customer Groups (field name is 'Status'). The two classes are Third Paties and Intercompany. To calculate the consolidated Gross Margin in % I have to divide the total Gross Margin (Sales - Purchases to all Customers) by the Sales to Third Parties.

In made up a Alternate Status ("aktJahr") and the Data selction is set to "Third Parties".

This expression works fine and gives a correct result

=sum({aktJahr< Status = >} (Sales-Purchases))

But if I try to divide by

sum(Sales) 

the Result is wrong

I tried this:

=sum({aktJahr< Status = >} (Sales-Purchases)) / sum({aktJahr< Status = { 'Third Parties' }>} VK_Wert )

but it produces the same mistake.

Does someone have an idea?

Thanks

Stephan

[#2]

Sorry I made a small mistake. To avoid misunderstanding the last expression should read:

=sum({aktJahr< Status = >} (Sales-Purchases)) / sum({aktJahr< Status = { 'Third Parties' }>} Sales )

[sunny_talwar]

Have you tried looking at just Sum(Sales) expression to check if that is giving the right number or not? I would say that provide us with a sample and expected output and one of us will def. try to help you out here

Best,

Sunny

[#4]

Dear Sunny,

thanx for your reply.

Yes, I checked it and it gives me the correct result. In fact both versions the simple sum(sales) as well as the set analysis expression work fine.

While working on another problem (comparison YTD vs previous YTD) I found the solution:

=num(sum({aktJahr< Year={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'},Status = >} (Sales - Purchase))/SUM({<Year={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'}>} Sales) , '0,0%')

I got input searching for a solution in the Resource library (http://community.qlik.com/docs/DOC-4313)

Certainly I will need the help from the community in the future......

Best regards

Stephan

[sunny_talwar]

Awesome, I am glad you were able to get your problem resolved . Mark you response as correct to close this thread down.

Best,

Sunny