Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to convert the following expression in to set analysis
Sum(If( Aggr(nodistinct Min(Datetime), BusinessID) = Datetime, OpeningBalance))
This may not be suitable for set analysis - you will still need to loop over all the BusinessID values to determine the minimum date so the performance may not be a lot better than the Aggr(). This better done with a flag value derived in the load script.
Map_MinDates:
Mapping LOAD BusinessID,
Min(DateTime) as MinDateTime
Resident Data
Group By BusinessID;
Join(Data)
LOAD BusinessID,
Datetime,
if(Datetime = ApplyMap('Map_MinDates', BusinessID), 1, 0) as MinDateFlag
Resident Data;
Then the expression becomes:
Sum({<MinDateFlag = {1}> OpeningBalance)
Change the tablename above (Data) to the correct name for your model.
hi
Thanks for the reply
But is it possible using set analysis only, can you help me ?
Thanks in advance
Try this,
I don't think whether nodistinct require for you
Aggr(Sum({<Datetime = {'$(=Min(Datetime))'}>}OpeningBalance),BusinessID)
We can help you if you provide data / application
I don't think it can be done practically using set analysis, but I would be interested to see if anyone can prove me wrong. And even if it is possible, it will be complex and unlikely to perform materially better than a sum(if())
Hi,
As you know set analysis evaluate set on chart level not on row level, there fore even if we convert it into set analysis still it not going work properly.
So i think jonathan suggestion is perfect solution for you.
Regards,
Prashant
Anil
The Min(Datetime) in that set expression is the minimum for all BusinessID's. I think the requirement is for the Min(Datetime) be the earliest Datetime for each BusinessID.
Let him explain more. I got his intention. That'f for i liked it your first reply. Anyhow, we can try different way if he prove himself or else we can suggest him as your first response
hi
Jonathan , your reply was helpful
But i have a qliksense qvf in which i have a Binary load data model , i cannot make changes in data load editor .
So , i am trying to do it on the presentation layer
hi
I tried this , but it is not giving me the value