Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Calculation Min Date in the Sum expression

Hi Experts!

I've just faced with the problem of the following calculation:

I the sample data model i have two non-linked table (it's the idea of this task and they shouldn't be related).

First table:

MainDate

Sales

 

Second Table:

FXDate

FXRate

 

In the pivot table i have the calculated WeekStart dimension based on the MainDate and i am calculating the Rates on those WeekStart days using the following expression:

=sum(IF(MainDate=FXDate, FXRate))

The problem is, that this expression is calculating the sum of 7 records from the FXRate table, but i need to take only the min date of each week.

 

=sum(IF(Min(MainDate)=FXDate, FXRate)) does not work, as it uses the nested min function, which should be aggregared.

The expected result - to fetch the FXRates per each WeekStart dates via the set analysis update.

 

Would be grateful for each idea!
Thanks.

6 Replies
Highlighted
Partner
Partner

I think I understand what you are trying to accomplish.  Because the dates to actually relate, you don't want to link the tables.  Makes sense.  I think you have two options here.

1.  Use a Calendar table and link the dates from each table to the calendar.  With a Calendar table, you can create a Weekstart() field, and by keying your Main Date and FX Date to the Calendar, your dates will bee associated appropriately.

2.  Concatenate the two tables and label the date fields the same name, so you can select values accordingly.  

Personally, I would try and make the calendar work.  I like Calendar tables, because they are very robust.  However, for the sake of simplicity, I have concatenated your two tables together and uploaded the app.  Take a look and see if this meets your needs.  

 

Nate

Highlighted

Is this what you are looking to get?

image.png

Highlighted
Contributor
Contributor

Thanks, Sunny!
Your solution works perfectly, but it's still applicable when changing the field value in the script.

I am looking for some other solution with the set analysis update only, meaning data mode/script logic stay the same.

Thanks for your feedback!

Highlighted
Contributor
Contributor

Thank you for your advice!
I agree with you on the advantages of using Calendar tables, but this is the really specific cases which needs no changes in the data model.

 

I am trying to solve the issue using the set analysis only.

Highlighted

If you don't want your expression to change based on selection, you can try this

 

=Sum({1} If(WeekStartMainDate = FXDate, FXRate))

 

image.png 

Highlighted
Digital Support
Digital Support

Andrey, did Sunny's last post get you what you wanted?  If so, please be sure to use the Accept as Solution button on that post to give him credit for the assist and let everyone else know that worked for you.  If you did something else, consider posting that and then mark that as the solution, and if you are still working on things, leave an update for everyone.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.