topic Re: set analysis 2 - dynamic dimension? in QlikView

set analysis 2 - dynamic dimension?

jakobjosef — Mon, 13 Aug 2018 15:09:26 GMT

I already thought that the AGGR function should help, but unfortunately it did not.

Here is some easy data: (Document attached as well)

Gruber and Jeremiah never sold something. I want this view:

Employee Working hours

Abu 48

Jack 48

John 48

Gruber & Jeremiah never sold anything, so I dont want them in the List.

fyi: "if(SalesAmount>0,Sum(WorkingHours)" doesn't work, cause QV just sums up (ONLY) the days where something got sold.

I am looking forward to your proposals.

Greetings
Jakob

Re: set analysis 2 - dynamic dimension?

vishsaggi — Mon, 13 Aug 2018 15:17:56 GMT

But there are few missing sales for employees on few dates, you want to consider hours for those dates with out sales amount?

Try this as your expression:

= Sum({<[Sales Amount] = {">0"} >} [Working hours])

Re: set analysis 2 - dynamic dimension?

jakobjosef — Mon, 13 Aug 2018 15:28:28 GMT

Thank you for your answer, Vishwarath.

The sales are not missing, there are just no sales on this dates. Employees do not sell something every day.

So yes, I want to consider hours for those dates without sales amont.

I tried your proposal, unfortunately it just sums up the hours of days, on which something got sold. But I want to sum up ALL hours, if there was a sale at any time.

Re: set analysis 2 - dynamic dimension?

chrismarlow — Mon, 13 Aug 2018 15:39:30 GMT

After an initial false start - how about;

If(aggr(sum([Sales Amount]), Employee, MthYear)>0,Sum(Total <Employee, MthYear> [Working hours]),0)

Where MthYear is a calculated field added at load.

Regards,

Chris.

Re: set analysis 2 - dynamic dimension?

vishsaggi — Mon, 13 Aug 2018 15:41:04 GMT

Try this in your straight table

Add Dim: Employee

Expr:

= Sum(IF(Aggr(NODISTINCT Sum([Sales Amount]), Employee)=0, 0,[Working hours]))

Re: set analysis 2 - dynamic dimension?

jakobjosef — Tue, 14 Aug 2018 06:12:47 GMT

Hi Vishwarath,

thank you for your answer. With your expression, all values are summed up (not just the ones of the month I seleced), but i know how to deal with this problem.

Thank you for your help

Greetings
Jakob

Re: set analysis 2 - dynamic dimension?

jakobjosef — Tue, 14 Aug 2018 06:14:05 GMT

Hi Chris,

your solution works as well, thank you for your help, I really appreciate it.

Have a nice day

Jakob