topic Set Expression with different modifiers for the same field in QlikView

Set Expression with different modifiers for the same field

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Expression with different modifiers for the same field

Mon, 12 Sep 2016 09:35:46 GMT

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 )

Re: Set Expression with different modifiers for the same field

sunny_talwar — Mon, 12 Sep 2016 16:08:11 GMT

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

Re: Set Expression with different modifiers for the same field

Tue, 13 Sep 2016 08:44:34 GMT

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

Re: Set Expression with different modifiers for the same field

sunny_talwar — Wed, 14 Sep 2016 01:21:54 GMT

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

Best,

Sunny