topic Re: Expression Help ??? in QlikView

Expression Help ???

amit_saini — Fri, 21 Apr 2017 04:43:44 GMT

Hi Folks,

I need help in below logic.

I'm having shift wise stacked bar chart which is showing "Pieces Produced" across 3 shifts , like below:

Dim : 1. Date

2. Shift_Name

Expression:

Pieces Produced : =num(Count(DISTINCT Serial))

Now I have to calculate "Total Pieces Produced " and the logic for this should be :

TOTAL PIECES PRODUCED = Above Calculation of Pieces Produced + [NO CAQ SCRAP FLAG]

So condition for [NO CAQ SCRAP FLAG] is vNScrapFlag =SUM( {$<Type_Name = {'Scrap'},Production = {'NoCAQ'}>} Quantity)

I'm trying below expression to achieve Total Pieces , but it not working as expected :

=num(Count(DISTINCT Serial)) + vNScrapFlag

See below:

As you can see above in Scrap Produced graph , we are having count "2" in Spat shift and "16" in Nacht Shift , so in bar chart of Total Pieces Produced it should add 2 to 243 Spat shift and 16 to 302 Nacht Shift.

By using =num(Count(DISTINCT Serial)) + vNScrapFlag , it is adding "18" to all the shifts which is wrong. The correct Output should be as marked in yellow circle.

I hope I'n clear here!

Please suggest how I can achieve this.

Attached is demo qvw.

Thanks,

Re: Expression Help ???

amit_saini — Fri, 21 Apr 2017 05:16:32 GMT

Any Suggestions???

Thanks,

Re: Expression Help ???

Anil_Babu_Samineni — Fri, 21 Apr 2017 05:21:18 GMT

Are you expecting changes over here - Total Pieces Produced stacked by shift ??

Re: Expression Help ???

amit_saini — Fri, 21 Apr 2017 05:33:22 GMT

Yes Anil.

Thanks,

Re: Expression Help ???

kaushiknsolanki — Fri, 21 Apr 2017 05:40:11 GMT

Hi,

The shared qvw file shows the same result as you want?

Please help me understand what exactly you need.

Regards,

Kaushik Solanki

Re: Expression Help ???

Anil_Babu_Samineni — Fri, 21 Apr 2017 09:38:19 GMT

Is this you are expecting

I have removed Shift_Name and i added

Calc. Dimension:

=ValueList('Früh','Nacht','Spät')

Expression:

If(ValueList('Früh','Nacht','Spät') = 'Nacht',num(SUM( {$<Shift_Name = {'Nacht'}, Type_Name = {'Scrap'}>} Quantity)), If(ValueList('Früh','Nacht','Spät') = 'Spät',

num(SUM( {$<Type_Name = {'Scrap'}, Shift_Name = {'Spät'}>} Quantity)),

If(ValueList('Früh','Nacht','Spät') = 'Früh',num(Count(DISTINCT {<Shift_Name = {'Früh'}>} Serial)))))

Re: Expression Help ???

amit_saini — Fri, 21 Apr 2017 09:41:15 GMT

Hi Anil,

Thank you!

I will try this also and update you.

But I got the solution by using below expression:

num(Count(DISTINCT Serial)) + SUM({$<Type_Name = {"Scrap"}>} Quantity) -SUM( {$<Type_Name = {'Scrap'},Production = {'CAQ'}>} Quantity)

Thanks,

Re: Expression Help ???

Anil_Babu_Samineni — Fri, 21 Apr 2017 09:44:19 GMT

Good, And this expression gives me this. Is this you are expecting??

Re: Expression Help ???

amit_saini — Fri, 21 Apr 2017 09:47:55 GMT

Yes Anil!

Thanks,

Re: Expression Help ???

Anil_Babu_Samineni — Fri, 21 Apr 2017 09:50:03 GMT

Haha, I thought that is x not the + . Anyway, Thanks for coming back

Re: Expression Help ???

amit_saini — Fri, 21 Apr 2017 09:51:28 GMT

Your Welcome!

Thanks,