Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Tarig
Contributor III
Contributor III

Calculate dimension

I created Calculated Dimension in QlikSense , but I got error message for Invalid Dimension. I am trying to create a Line chart that represent the number of projects by working days by year, so:

1- My first dimension is working year 

2- My second dimension is an expression to get the numbers of working days by year, but it's not working not sure why? Here is the expression 

= If(count(Distinct WorkingDate) = 1, '1',
    If(count(Distinct WorkingDate) = 2, '2',
    If(count(Distinct WorkingDate) = 3, '3',
    If(count(Distinct WorkingDate) = 4, '4',
    '+5'))))

3- My Measures is the Count of ProjectId

 

so I should have a chart that looks like this:

 

InkedworkingDays_LI.jpg

 

Thank you for your time

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

try this.

aggr(If(count(Distinct If(IsPrepDate =0,WorkingDate)) = 1, '1',
             If(count(Distinct If(IsPrepDate =0,WorkingDate))= 2, '2',
             If(count(Distinct If(IsPrepDate =0,WorkingDate)) = 3, '3',
             If(count(Distinct If(IsPrepDate =0,WorkingDate)) = 4, '4',
              '+5')))),ProjectId)

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Enclosed the expression in another expression called aggr, like below.

=aggr(If(count(Distinct WorkingDate) = 1, '1',
    If(count(Distinct WorkingDate) = 2, '2',
    If(count(Distinct WorkingDate) = 3, '3',
    If(count(Distinct WorkingDate) = 4, '4',
    '+5')))),ProjectId)

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Tarig
Contributor III
Contributor III
Author

Awesome, thank you so much.  I also want to add a new condition to this expression by counting the distinct work days only if the prepDate is equal to zero, I tried to do it in this way but is not giving the right answer:

 

=aggr(If(IsPrepDate= 0 and count(Distinct WorkingDate) = 1, '1',
             If(IsPrepDate= 0 and count(Distinct WorkingDate) = 2, '2',
             If(IsPrepDate= 0 and count(Distinct WorkingDate) = 3, '3',
             If(IsPrepDate= 0 and count(Distinct WorkingDate) = 4, '4',
              '+5')))),ProjectId)

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

try this.

aggr(If(count(Distinct If(IsPrepDate =0,WorkingDate)) = 1, '1',
             If(count(Distinct If(IsPrepDate =0,WorkingDate))= 2, '2',
             If(count(Distinct If(IsPrepDate =0,WorkingDate)) = 3, '3',
             If(count(Distinct If(IsPrepDate =0,WorkingDate)) = 4, '4',
              '+5')))),ProjectId)

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Tarig
Contributor III
Contributor III
Author

You are the best, thank you so much, That solved my problem