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: 
wale_martins
Contributor III
Contributor III

Select an specific date value by dimension in set analysis when using a date range

Hi,

In one column I'm using a set analysis expression to get all sum(values) between a date range.

Example: sum({<[Date_Operation] = {">=$(=date(vStartDate))<=$(=date(vEndDate))"}>} Aggregate_Diary_PL_Stock_Accum)

With this expression there's no problem and I can get the corresponding value for each dimension in each date.

In another column I'm using set analysis to get the sum(values) in a specific date "vStartDate-1"

Example: sum(total {<[Date_Operation] = {"$(=date(vStartDate-1))"}>} Aggregate_Diary_PL_Stock_Accum)

The problem that I have with that expression is that I need to use 'total' to get the sum(values) correctly, but then I can't get the value for each dimension.

I've already tried total <dimension> but it doesn't work... it only works when using vEndDate intead of vStartDate-1, that's why I suppose that the problem is the Date_Operation of each row.

So, In the second expression I would have to avoid the Date_Operation field, but I can't manage it.

Any help would be appreciated.

Thanks in advance.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Hi have worked for one expression and left some for you. 😋 . The main change in expression is use of dimensionality() and replacing set analysis with IF.

If([Date_Operation] >='$(=date(vStartDate))' and [Date_Operation]<='$(=date(vEndDate))',
	If( Dimensionality()=2
	,Aggr( sum(TOTAL <Ticker> If([Date_Operation] ='$(=date(vStartDate-1))', Aggregate_Diary_PL_Stock_Accum)), Date_Operation,Ticker)
	  ,sum(TOTAL {<[Date_Operation] = {"$(=date(vStartDate-1))"}>} Aggregate_Diary_PL_Stock_Accum)
	)
	,
	0
	)   

Capture.PNG

 

View solution in original post

13 Replies
wale_martins
Contributor III
Contributor III
Author

Sorry to bother you @sunny_talwar, but there is no reply to my post and I'm sure that you have the solution.

Best Regards

Wale

 

tresesco
MVP
MVP

I guess you are on the right path, Total is required to be used here. Could you explain with a sample or screenshot what you are getting using total identifier and what you are looking for?

wale_martins
Contributor III
Contributor III
Author

Hi @tresesco ,

Thank you for your reply. Attached you have an example.

Collapsed Data

In that case 'P&L StockAccum.' calculation is correct. 4,343.98 is the correct total (all tickers) value for 'date(vStartDate-1)'

Expanded Data (Example 30 January)

In that case 'P&L StockAccum.' calculation is incorrect. As you can see, for each ticker I'm assigning the 'date(vStartDate-1)' total (all tickers) value and not the ticker one.

I don't know if I'm explaining myself well.

 

tresesco
MVP
MVP

A sample qv file would have been of more help in knowing what expression, dimensions...you are using. Could you share one? Else, help explaining the dimensions you are using in chart (possibly pivot) and exact expression you are using.

Assuming you are expecting different number for every tickers, in other words, respect the ticker dimension.    

wale_martins
Contributor III
Contributor III
Author

Sorry @tresesco ,

But I can't share the qv file (sensitive information in it). Let me prepare a sample. I'll attach it as soon as possible.

Thanks

wale_martins
Contributor III
Contributor III
Author

Hi @tresesco ,

Attached you have a qv example with its dataset

Thank you very much

Wale

wale_martins
Contributor III
Contributor III
Author

sorry @tresesco 

This qv file will be clerarer.

 

tresesco
MVP
MVP

What if you try :

sum( total <Ticker>  {<[Date_Operation] = {"$(=date(vStartDate-1))"}>} Aggregate_Diary_PL_Stock_Accum)

wale_martins
Contributor III
Contributor III
Author

Hi @tresesco,

That was one of the first options that I tried... but all values were converted into "0"